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]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=IF(Dashboard!$D$19="ALL",COUNTIF('CIPS Register'!C:C,C7),sumproduct(('CIPS Register'!E:E=Dashboard!$D$19)*(year('CIPS Register'!C:C)=C7)))

 

[Taz1pop]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=IF(Dashboard!$D$19="ALL",COUNTIF('CIPS Register'!C:C,C7),sumproduct(('CIPS Register'!E:E=Dashboard!$D$19)*(year('CIPS Register'!C:C)=C7)))

Thank you, i have just updated my account details (and saved ). I am using 365.

 

[Taz1pop]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=IF(Dashboard!$D$19="ALL",COUNTIF('CIPS Register'!C:C,C7),sumproduct(('CIPS Register'!E:E=Dashboard!$D$19)*(year('CIPS Register'!C:C)=C7)))

I have tried the formulae you proposed but it just returns back #Value.

 

[Fluff]

Oops, put the year in the wrong part, try

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

 

[Taz1pop]

Oops, put the year in the wrong part, try

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

That still comes back with #value when I change the year.

 

[Fluff]

Roughly how many rows of data do you have?
Also does column E contain a formula that returns ""

 

[Taz1pop]

Roughly how many rows of data do you have?
Also does column E contain a formula that returns ""

at the moment as the form isnt live there is only some example data in the first 7 rows.
Column E has no formulas just the cells formatted as short Date.

What i am trying to do is get a pie chart on the fron tab (Dashboard) which changes when the year and site is selected. At the moment i am just trying to get it to work with year. After that ill then try and get site included.

Outcome is anyone can see how many open closed items there were total, or per site and then total per year per site.

hope that explains what i am trying to achieve.

 

[Fluff]

Ok, what row does the data start on?

 

[Taz1pop]

Ok, what row does the data start on?

row 5