• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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
0
cookiejest
Asked:
cookiejest
1 Solution
 
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
 
barry houdiniCommented:
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now