# multiple criteria in a countifs

Hi there,

I have this excel formula:

=COUNTIFS( 'Request History Raw'!\$W:\$W,">="&DATE(B\$16,B\$17,1), 'Request History Raw'!\$W:\$W, "<"&DATE(B\$16,B\$18,1),'Request History Raw'!\$K:\$K,\$A20,'Request History Raw'!\$R:\$R,\$B\$3)

The issue is with the last bit "'Request History Raw'!\$R:\$R,\$B\$3)

I have multiple cells that need to be included in the last criteria where \$B\$3 is. Can you advise on how to account for this?

I know that I could repeat the statement for each criteria by adding them like this:

COUNTIFS( 'Request History Raw'!\$W:\$W,">="&DATE(B\$16,B\$17,1), 'Request History Raw'!\$W:\$W, "<"&DATE(B\$16,B\$18,1),'Request History Raw'!\$K:\$K,\$A20,'Request History Raw'!\$R:\$R,\$B\$3) + COUNTIFS( 'Request History Raw'!\$W:\$W,">="&DATE(B\$16,B\$17,1), 'Request History Raw'!\$W:\$W, "<"&DATE(B\$16,B\$18,1),'Request History Raw'!\$K:\$K,\$A20,'Request History Raw'!\$R:\$R,\$B\$3) + COUNTIFS( 'Request History Raw'!\$W:\$W,">="&DATE(B\$16,B\$17,1), 'Request History Raw'!\$W:\$W, "<"&DATE(B\$16,B\$18,1),'Request History Raw'!\$K:\$K,\$A20,'Request History Raw'!\$R:\$R,\$B\$3)...

but that is extremely messy there must be a better way of doing it
###### Who is Participating?

Commented:
If you want to match against a range try like this for range B3:B10

=SUMPRODUCT(COUNTIFS( 'Request History Raw'!\$W:\$W,">="&DATE(B\$16,B\$17,1), 'Request History Raw'!\$W:\$W, "<"&DATE(B\$16,B\$18,1),'Request History Raw'!\$K:\$K,\$A20,'Request History Raw'!\$R:\$R,\$B\$3:\$B\$10))

Note: the whole formula is wrapped in SUMPRODUCT because when you change B3 to B3:B10 the COUNTIFS function returns an "array" of 8 results (one each for B3, B4, B5 etc.) so the SUMPRODUCT is then used to sum the array

regards, barry
0

EngineerCommented:
You have repeated the countif 3 times without any difference the three

If this is the case then you can simply multiply \$B\$3 by 3 or whatever

\$B\$3*3

If this is not the case then there should be some difference between the three countifs. Please upate.
0

Commented:
For the general information of all - here is a very good article on the usage of the SUMPRODUCT sheet function when the objective is to calculate in a range with multiple criteria:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.