COUNTIFS between Dates

**simonwar** · 12-19-2021, 06:56 PM

Hi,

I am having a bad morning and for some reason cant get a COUNTIFS to work, throwing a #VALUE error?

I have this data as an example, see attached.

P = PLANNED
C=COMPLETED
Cld = CANCELLED

and I am simply trying to look across all dates for "P","C" & "Cld", Events to determine;

How many PLANNED events I have, how many COMPLETED, etc... per Quarter.

For example:
Report is by Quarter, So Jan-Mar, Apr-Jun, etc

Jan-Mar Planned = 1
Completed = 1
Cancelled = 1

Apr-Jun Planned = 3
Completed = 1
Cancelled = 1

etc...

I am sure i have done this before?

Any help would be appreciated.

**Bernie Deitrick** · 12-19-2021, 07:44 PM

Your ranges need to have the same shape (row counts and column counts equal).

So, you can use this in J8, copied to J8:L13

=COUNTIFS($D$7:$D$21,J$7,$C$7:$C$21,">="&$I8,$C$7:$C$21,"<"&$I9)+COUNTIFS($E$7:$E$21,J$7,$C$7:$C$21,">="&$I8,$C$7:$C$21,"<"&$I9)+COUNTIFS($F$7:$F$21,J$7,$C$7:$C$21,">="&$I8,$C$7:$C$21,"<"&$I9)

You would be much better off if you had a table of all events with a date column, some descriptor, and a status (Planned, Completed, Cancelled) and then you could just use a pivot table with no formulas.

**simonwar** · 12-19-2021, 08:06 PM

Oh.....I thought so.

This works so I am having to COUNTIFS for each Event and Add the results together...... I get it :-)

I have never tried PIVOTS, i'll have a look into this.... thanks for the advice.

Simon