Count several criteria for days off

[summer2010]

This is in relation to my post yesterday 26th Sept at 1.42.

Firstly many thanks to those who replied,but I realise that I need it to count the number of Days in January that some one has had off sick for the reason of i.e. Back Pain
I've put in the formula suggested into K78 =SUMPRODUCT(($A$17:$A$62=K$75)*($G$17:$G$62=$I78)) but it shows up Jan.

What I'm trying to achive is
if A17:A62 ="January" and G17:G62 = Back pain, then it needs to count number of days off in D17:D62 that match those criteria and put totals into K78. and so on. Hopefully I've explained it ok here:

I've attached the file and hopefully this will show more of what I'm trying to explain

I can't thank you enough for your help

[Harley2]

Summer2010,

You need one more piece to get the number of days, I adjusted your fomula and changed the grid with the formula to Format "General". Modified file attached.

[Charlie_Howell]

try this and see if this works for you.

[daddylonglegs]

Hello Charlie,

If summer2010 has correct details then SUMIFS won't be an option as that function doesn't appear in Excel 2003.

Harley2,

If you use SUMPRODUCT then *-- should be avoided. Normally one or the other i.e. either

=SUMPRODUCT(--($A$17:$A$62=K$75),--($G$17:$G$62=$I78),$D$17:$D$62)

or

=SUMPRODUCT(($A$17:$A$62=K$75)*($G$17:$G$62=$I78),$D$17:$D$62)

[summer2010]

Hi

Wow!! I can't appreciate all your help on the formulas. It's very difficult when you are asked to create something on Excel (To which I know very little about) and there is no one to ask for help in the office. I'm using 2003 so sadly the suggestions by Charlie won't work, but thanks again Charlie for your help.

I'll have a look at the other suggestions when I'm able to look at the spreadsheet in the next couple of days and feed back to you. :-)

[Charlie_Howell]

Sorry I couldn't help, I should have looked at what version the sheet was written. Good luck in your excel career and upgrade to 2007 or 2010 as soon as you can so I don't look so silly next time:-P
Harley, You rock!

[summer2010]

Hi Charlie

It's very frustrating not having 2007at work as it looks like you could do so much more. Fingers crossed that I have 2007 in the near future, although i'm not holding my breath!!

Summer2010

[summer2010]

Thanks for all your help,

Daddylonglegs I've used your second formula suggested and it works perfectly. I've now completed my spreadsheet and can now work on the other half of the work book. :-)