CODEWITHSUNDEEP
excelexcel-formula
marcp
marcp

Reputation: 1227

Excel COUNTIFS with mixed dates and logicals

I am trying to calculate the number of items between two dates AND where a yes/no field is Yes

I've come up with this:

=COUNTIFS( $O$1:$O$601,"=Yes",$D$2:$D$601,"<September 9, 2013 20:00",$D$2:$D$601,">September 9, 2013 17:45")

This gives a #VALUE! result, even though either of these work 

=COUNTIFS( $O$1:$O$601,"=Yes")
=COUNTIFS($D$2:$D$601,"<September 9, 2013 20:00",$D$2:$D$601,">September 9, 2013 17:45")

is there a limitation on mixing types of comparisons?

Upvotes: 0

Views: 351

Answers (2)

manimatters
manimatters

Reputation: 434

From the Excel Help for the function COUNTIFS:

Each additional range must have the same number of rows and columns as the criteria_range1 (first range) argument. The ranges do not have to be adjacent to each other.

So you need to make them the same size.

Upvotes: 0

barry houdini
barry houdini

Reputation: 46341

You just need all ranges to be the same size. Your first range starts at row 1 the others start at row 2 - if you make them all start at row 2 it should work OK

=COUNTIFS($O$2:$O$601,"=Yes",$D$2:$D$601,"<September 9, 2013 20:00",$D$2:$D$601,">September 9, 2013 17:45")

Upvotes: 1

Related Questions