Formula needed to count if data is specific to a year

**katieshields** · 05-30-2013, 08:25 AM

Hi all, I am working on a spread sheet which you guys have already helped me with. I now want to count the number of days employees have spent training in a particular year.

I have attached an example for you. The course duration is in column 4 just under the cell Éxpiry". If the training courses were Completed in 2013 I want to add the course durations for each employee.

I hope this makes sense.

Thanks

Katie

**Ace_XL** · 05-30-2013, 09:08 AM

In F5, try this array formula

=SUMPRODUCT(--(IFERROR(YEAR(G5:N5),0)=2013),$H$4:$O$4)

Confirm with Ctrl+Shift+Enter and not just Enter

Copy down

**katieshields** · 06-01-2013, 06:46 AM

Thank you, this works for these columns but when I amend it slightly to go across all the other columns it is sending back #VALUE

I have attached the full example

**Ace_XL** · 06-01-2013, 08:36 AM

In F5 use

=SUMPRODUCT(--(IFERROR(YEAR($G5:$BL5),0)=2013),$H$4:$BM$4)

copy down

Remember to Ctrl+Shift+Enter

**katieshields** · 06-02-2013, 12:29 AM

I have a quick question though. People on here have given me a few formulas now that you need to press Ctrl+Shift+Enter, why is this?

**katieshields** · 06-02-2013, 12:29 AM

But other than that question the formula works perfectly. Thank you so much

**dredwolf** · 06-02-2013, 12:34 AM

Cntrl+Shift+Enter makes the formula an Araay Formula, meaning that it looks at arrays of numbers to do its calculations..which gives the correct response for formulas dealing with multiple entries/conditions (this is a very simplified explanation)