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
cookiejestAsked:
Who is Participating?
 
barry houdiniConnect With a Mentor 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
 
Saqib Husain, SyedEngineerCommented:
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
 
gtglonerCommented:
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.

All Courses

From novice to tech pro — start learning today.