SOLVED

COUNTIFS problem

New Contributor

Hi,

 

I'm trying to use the COUNTIFS formula to count cells in which the criteria are:

 

Column T2:T3031 "Other"

Column T2:T3031 "Temporary"

Column W2:W3031"0"

 

I want the formula to result in a total count of rows in which "other" appears in column T with "0" in column W, and then "Temporary" appears in column T  with "0" in column W. 

 

Thanks for any help.

3 Replies

@Ellbeardo 

=SUMPRODUCT(((T2:T3031="Temporary")+(T2:T3031="Other"))*(W2:W3031=0)*(W2:W3031<>""))

You can try SUMPRODUCT:

countifs.JPG 

best response confirmed by Ellbeardo (New Contributor)
Solution

@Ellbeardo 

Hi Ellbeardo,

 

If I've understood you correctly, you have a data set like the one I've shown in the attached image (your column T is my column A, and your W is my B), in which Column A contains various values, including "Other" and "Temporary", against which, in column B, there can be various values, but you want to count only the pairs where A= either "Other" or "Temporary" and B=0 (which I have highlighted yellow in the image.)

 

You could do this by summing 2 COUNTIFS() calls like this:

 

=COUNTIFS(A1:A19,"Other",B1:B19,0)+COUNTIFS(A1:A19,"Temporary",B1:B19,0)

 

or like this (same thing really)

 

=SUM(COUNTIFS(A1:A19,"Other",B1:B19,0),COUNTIFS(A1:A19,"Temporary",B1:B19,0))

 

ColinJHarrison_0-1669325152724.png

 

Hope that helps

 

Colin

 

Brilliant, thanks!

I did try that first one but I think I missed the second criteria in the second COUNTIFS! Sorted now.