COUNTIFS with single criteria but multiple column range

[oceaner]

I'm trying to count # of times a number (coded #) appears over a range (C:Z) in a given month of a given year. Sample data below. The only solution I could come up with was using COUNTIFS, but that only works to count one column at a time

=COUNTIFS(C:C,1,A:A,">="&AH1,A:A,"<"&AI1)

ideally I'd want

=COUNTIFS(C:Z,1,A:A,">="&AH1,A:A,"<"&AI1) which doesn't work

the only solution seems to be to do

=COUNTIFS(C:C,1,A:A,">="&AH1,A:A,"<"&AI1) + COUNTIFS(D:D,1,A:A,">="&AH1,A:A,"<"&AI1) until reaching Z:Z which is extremely redundant, specially since I want to do this over many numbers.

Any solution would be appreciated.

sample data:

 

[jtakw]

Hi,

Try it like this, change/adjust cell references/range as needed, please stay away from Entire Column references:

Book1
	H
1	0
Sheet344

Cell Formulas
Range	Formula
H1	=SUMPRODUCT((A1:A1000>=AH1)*(A1:A1000)*(C1:Z1000=1))

 

[oceaner]

Hi,

Try it like this, change/adjust cell references/range as needed, please stay away from Entire Column references:

	H
1	0

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet344​

Worksheet Formulas Cell Formula H1 =SUMPRODUCT((A1:A1000>=AH1)*(A1:A1000<AI1)*(C1:Z1000=1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you so much! appears to be working like a charm. Didn't realize SUMPRODUCT could do this. Why are entire column references bad if I may ask?

 

[jtakw]

Thank you so much! Why are entire column references bad if I may ask?

You're welcome, Yes SUMPRODUCT can also be used to COUNT (especially useful in arrays as in your case) when set up properly.

Entire Column references, Try it, you'll see that your Worksheet will Slow Down dramatically...(there's over 1 million rows in Each column for the formula to process)

 

[oceaner]

Oh that makes sense!