how to count multiple columns using countifs

问题:

I am putting in a formula to count the number of times a quote is required Indicated by the letter Q in a given column, when I put the formula in for one column I get the correct answer, but when I want to do it for multiple columns I get zero, can anyone help please? the formula is

=COUNTIFS(D10:D29,"=Q",G10:G29,"=Q")

回答1:

Try either

=SUMPRODUCT((D10:D29="Q")+(G10:G29="Q"))

or

=SUMPRODUCT(((D10:D29="Q")+(G10:G29="Q")>0)+0)

the former will count 2 if you have Qs in both D10 and G10 - the latter only counts each row once at most, even if there are two "Q"s

回答2:

countifs criteria are connected by a logical AND. so that formula is saying it must find your string in column D AND in column G. Apparently there are 0 instances of that. if you want the total number of cells with it then make it one range.

If the must be non-contiguous, use multiple countif formulas and add them

as a note, here I would change my formula back to countif, instead of countifs for backwards compatibility since I don't use the extra criteria.

EDIT: my second example was incorrect (See comments) so I removed it

回答3:

Actually, what I've found is that there is a way better way instead of the sumproduct, which can result in a overly-long formula if you have 5 columns. Instead, I found that using the SUM+IF function as we use the SUMPRODUCT, will achieve the result faster and better.

=SUM(IF((E:I="ABC")*(B:B="DEF"); 1; 0))

This function returns the number of rows that contain both "ABC" and "DEF" in the defined columns.

Spread the word!