countifs

[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

 

[T. Valko]

One way:

=SUM(COUNTIFS(A2:A10,"name",C2:C10,"sold",D210,{"new";"under"}))

 

[T. Valko]

Ooops!

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

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

 

[Jacob Skaria]

Using SUMPRODUCT()

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

If this post helps click Yes

 

[Jacob Skaria]

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

 

[Trev]

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'}))

 

[T. Valko]

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.

 

[Jacob Skaria]

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

 

[T. Valko]

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!

 

[Trev]

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

 

[Ashish Mathur]

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

 

[Trev]

Thanks everything works well!!!!!!!!!!!!!!

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.

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Trev]

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?

 

[T. Valko]

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!

 

[Trev]

This is returning a count of 0, should be 66. Any ideas. The last part does
not seem to working correctly.

 

[Jacob Skaria]

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

 

[Jacob Skaria]

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

 

[T. Valko]

There could be a myriad of reasons. See this:

http://contextures.com/xlFunctions02.html#Trouble

Although the article is describing another function the problems being
discussed apply to any and all functions.

You can try replacing this:

MATCH('Pending Sold'!S:S,{"new","under"},0)

With this:

SEARCH({"new","under"},'Pending Sold'!S:S)

 

[T. Valko]

Dude!

How about exercising some "net etiquette".