countifs formula help

[herbie.000]

Status (full) Issue Date (10/1/2014) Printed On (10/1/2014) Type (Animal Shelter or Animal Control Facility) Received Electronically (No)



What I need is to count items by 6 months (1-1-2014-6-30-2014 and/or 7-1-2014 -12-31-2014) then by the type (Animal Shelter or Animal Control Facility)-- then by received electronically (yes/no).

I have the below formula that counts by date range (using the Printed date), but I don't know how to add the received by electronically into this formula.

=COUNTIFS($C$3:$C$2996,">=1/1/2014",$C$3:$C$2996,"<=6/30/2014",$A$2:$A$2996,"Animal Shelter or Animal Control Facility")

At some point I might also need to add in that status (full, denied, void) as well, but that is of lesser need right now.

Any help would be greatly appreciated.

[The_Snook]

Hi Herbie,

You just need to add it in as an additional criteria (see red text). Change the blue x's to the relevant column reference letter(s).

=COUNTIFS($C$3:$C$2996,">=1/1/2014",$C$3:$C$2996,"<=6/30/2014",$A$2:$A$2996,"Animal Shelter or Animal Control Facility",$x$2:$x$2996,"No")

This formula just covers the first 6 months of 2014. If you wanted the last 6 months you would need to amend the dates in the formula accordingly.

As a potential alternative, you could probably achieve what you seek using a pivot table.

Hope this helps,

Snook

[herbie.000]

Thanks, when I tried to add it as another criteria I must have not had something right as it did not work, but it's working now.

I thought about using a pivot table as I have from 2014-2021 to do and about 100,000 rows each year. I can filter, but when filtering by the date range I have to click on or off each date. Unless someone knows how to put a rage in a a filter?

[The_Snook]

Thinking on the fly, if you pull the date data into the row/column area of the pivot table you should be able to specify a date range. If you want do it based on the filter field I think you'll have to add another column into your source data that identifies which period it relates to, e.g. 2014 1-6 or 2014 7-12 etc. You can then filter based on the specific time frame you are looking for, rather than having to check/uncheck a significant amount of tickboxes.

Hope this helps,

Snook