countifs

  • Thread starter Thread starter Trev
  • Start date Start date
T

Trev

I need a formula that counts if column C = Sold, and column A = Name and
column S and be either New* or Under*

=COUNTIFS(C:C,"sold",A:A,"name")can equal Name or Under

Is this possible?
Thanks
 
Ooops!

Used the wrong range for that last criteria. Should be:

=SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",S2:S10,{"new";"under"}))
 
Using SUMPRODUCT()

=SUMPRODUCT(--(A2:A100="name"),--(C2:C100="sold"),--ISNUMBER(MATCH(s2:s100,{"red","black"},0)))

If this post helps click Yes
 
Should be the below for wildcards...

=SUMPRODUCT((A2:A100="name")*(C2:C100="sold")*(ISNUMBER(SEARCH({"NEW","UNDER"},S2:S100))))

If this post helps click Yes
 
I am trying to get this to draw from another sheet and keep getting an error.
any ideas?


=SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending
Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under'}))
 
Works ok for me.

When I copied your posted formula and pasted it I got the general formula
error message: The formula you typed contains an error....

It highlighted this portion of the formula:

{"new";"under"}))

I just backspaced that out and retyped and it worked.
 
Trev; try copy paste the below (the last one was a single quote instead of
double quote which caused the error)

=SUM(COUNTIFS('Pending Sold'!A1:A10,"name",'Pending
Sold'!C1:C10,"sold",'Pending Sold'!S1:S10,{"new*","under*"}))

If this post helps click Yes
 
OK, now I see why I got that error message.

....'Pending Sold'!S:S,{"new";"under'}))

You're missing the second quote after under.

....'Pending Sold'!S:S,{"new";"under"}))

I have poor eyesight. That's why I didn't catch it the first time around.

One of these days I'm gonna have to bail out of these forums because of my
poor eyesight. Either that or I'm gonna have to get a 52 inch monitor and
view everything in a 40pt font size!
 
this works when I put it on one sheet but I need it to look for the info on
another sheet. When I try

=SUMPRODUCT(--('sheet'!A:A="name"),--('sheet'!C:C="sold"),--ISNUMBER(MATCH('aheet'!S:S,{"red","black"},0)))

I receive a return of 0
 
Hi,

You have misspelled sheet in the last portion - change the spelling to sheet
from aheet. Also, please avoid using entire columns as references

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Thanks everything works well!!!!!!!!!!!!!!

T. Valko said:
Works ok for me.

When I copied your posted formula and pasted it I got the general formula
error message: The formula you typed contains an error....

It highlighted this portion of the formula:

{"new";"under"}))

I just backspaced that out and retyped and it worked.
Click to expand...
 
Now I need to take this formula and make it count a range.

=SUM(COUNTIFS('Pending Sold'!A:A,"name",'Pending Sold'!C:C,"sold",'Pending
Sold'!S:S,{"new";"under"}))

I need to count column F on the Pending Sold sheet if it is >= 1 and F<=
150000 with Pending Sold'!C:C,"sold",'Pending Sold'!S:S,{"new";"under"}))

Make sense, Can you Help?
 
Try this:

=SUMPRODUCT(--('Pending Sold'!C:C="sold"),--('Pending
Sold'!F:F>=1),--('Pending Sold'!F:F<=150000),--(ISNUMBER(MATCH('Pending
Sold'!S:S,{"new","under"},0))))

Note: you should not reference *entire* columns using this formula unless
you *absolutely MUST*. *EVERY* cell referenced will be calculated. In the
above formula you're calcualting over 4,000,000 cells!
 
This is returning a count of 0, should be 66. Any ideas. The last part does
not seem to working correctly.
 
Tre; try the below...(Take a close look at these formulas and see whats the
difference between those to understand how they work.)

=SUMPRODUCT(--('Pending Sold'!C:C="sold"),
--('Pending Sold'!F:F>=1),
--('Pending Sold'!F:F<=150000),
--(ISNUMBER(SEARCH({"NEW","UNDER"},S2:S100))))

If this post helps click Yes
 
Correction; try the below

=SUMPRODUCT(--('Pending Sold'!A:A="name"),--('Pending Sold'!C:C="sold"),
--('Pending Sold'!F:F>=1),--('Pending Sold'!F:F<=150000),
--(ISNUMBER(SEARCH({"NEW","UNDER"},S:S))))


If this post helps click Yes
 
Back
Top