COUNTIFS and using year as one of the criteria.

[Taz1pop]

Hi All. I have gone round and round and still have no luck in resolving this, so any help will be much appreciated.

I have a list of items that have a status, (open, Closed, In progress). This is column C. There is also another column (E) which has a date (Short form DD,MM,YYY) which hs the date the item has been logged this can be over many years.

I then have a cell on another page (Dashboard) D19, that lets you select a year or the word ALL. (list is in a drop-down list)

I am trying that when the word ALL is selected its gives the percentage (in a pie chart) of each of the 3 statuses (i got this bit working) But when i select a year the countifs doesn't find the additional criteria for the year. I hope that makes sense.

The formulae i have at the moment is.
=IF(Dashboard!$D$19="ALL",COUNTIF('CIPS Register'!C:C,C7),COUNTIFS('CIPS Register'!E:E,Dashboard!$D$19,'CIPS Register'!C:C,C7))

 

[Fluff]

Ok, how about

=IF(Dashboard!$D$19="ALL",COUNTIF('CIPS Register'!C:C,C7),SUMPRODUCT((YEAR('CIPS Register'!E5:E1000)=Dashboard!$D$19)*('CIPS Register'!C5:C1000=C7)))

 

[Taz1pop]

Ok, how about

=IF(Dashboard!$D$19="ALL",COUNTIF('CIPS Register'!C:C,C7),SUMPRODUCT((YEAR('CIPS Register'!E5:E1000)=Dashboard!$D$19)*('CIPS Register'!C5:C1000=C7)))

Thats perfect thank you.
Now just to add in the other selector now for sites

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Taz1pop]

You're welcome & thanks for the feedback.

Would you mind explaining the sumproduct function please, this is new one to me.

Where ever I try putting in the site option it doesn't consider the site part. I managed to include the AND statement at the beginning so if they are both set to all then it's everything. It's just having the different criteria options below that can change, it doesn't do anything. Does it go before or after the multiplication.

I hope the above makes sense.

=IF(AND(Dashboard!$D$19="ALL",Dashboard!$D$20="All"),COUNTIF('CIPS Register'!C:C,C7),SUMPRODUCT((YEAR('CIPS Register'!$E$5:$E$5000)=Dashboard!$D$19)*('CIPS Register'!$C$5:$C$5000=C7)))

that's what I currently have

 

[Fluff]

For information on sumproduct have a look here SUMPRODUCT Function
What column should D20 match to?

 

[Taz1pop]

For information on sumproduct have a look here SUMPRODUCT Function
What column should D20 match to?

Thank you i will have a read.

D20 on the Dashboard should match column H on the CIPS register tab. The only issue is the first in the drop-down list in D20 is All so if that is selected then its all sites, and can filter by year, and then visa versa can hall all for each of the sites. Its the All bit which hurts my head lol.

 

[Fluff]

How about

=COUNT(FILTER(ROW('CIPS Register'!$C$5:$C$5000),('CIPS Register'!$C$5:$C$5000=C7)*(IF(Dashboard!$D$19="All",1,YEAR('CIPS Register'!$E$5:$E$5000)=Dashboard!$D$19))*(IF(Dashboard!$D$20="All",1,'CIPS Register'!$H$5:$H$5000=Dashboard!$D$20)),""))

 

[Taz1pop]

How about

=COUNT(FILTER(ROW('CIPS Register'!$C$5:$C$5000),('CIPS Register'!$C$5:$C$5000=C7)*(IF(Dashboard!$D$19="All",1,YEAR('CIPS Register'!$E$5:$E$5000)=Dashboard!$D$19))*(IF(Dashboard!$D$20="All",1,'CIPS Register'!$H$5:$H$5000=Dashboard!$D$20)),""))

That is perfect and seems to work perfectly.

I am speechless, thank you so much, that is amazing.

 

[Fluff]

You're welcome & thanks for the feedback.