Sum of Columns when criteria is met?

**Iscariot** · 02-26-2009, 07:37 AM

As requested, a thread to hopefully resolve an issue I'm having.

I need a formula that will do the following:

1) If B1:2008 is "EA" (and so on for about 7 other classes) then count how many days are entered in G8:2008 so a cell on a seperate distribution sheet tells me how many sickness days in total have been taken by EA's (and then the same for the other classes).

I can't seem to get a method that works. Help very muc appriciated!

**DonkeyOte** · 02-26-2009, 07:40 AM

So can you use SUMIF ?

=SUMIF(Sheet!B1:B2008,"EA",Sheet1!G8:G2008)

If you have multiple criteria that you wish to aggregate you can use:

=SUM(SUMIF(Sheet1!B1:B2008,{"EA","EX","ZY","DV"},Sheet1!G8:G008))

**Iscariot** · 02-27-2009, 05:35 AM

...Can't believe I didn't try sumif. I essentially tried to go too complex, using the method you showed me previously about creating a refence cell for the whole range. Simplist way never occured to me. Thanks Donkey :D

One last thing and then hopefully I'll finally be finished with this, is that at the moment on the Database, if someone has multiple sicknesses they're entered multiple times, so each one counts as a seperate period of absence - Is there anyway to create a formula that would be able to count the number of people in each grade who have been sick, ignoring multiples of the same person?

So if Column A for example looked like this

Mawdesley
Hancock
Hall
Hall
Hall
Hall
Hall
Allott
Allott
Cranston
Cranston

...and they were all EA's, the total would be 5. Is that possible, as on this one I just don't have a clue. Thanks in advance!

=I=

**DonkeyOte** · 02-27-2009, 05:58 AM

Assuming Names in A and EA etc in B:

Please Login or Register  to view this content.

This assumes however that any given named is associated with 1 area, eg "EA"... ie relationship between A:B is 1:1 ... if not a different approach would be required.

**Iscariot** · 02-27-2009, 07:02 AM

Tried Sumproduct, not getting any kind of result.

I don't expect the same (awesomely legendary) effort of last time, but I've attached a sample so you can see the layout and setup as I imagine it'll help seeing the relationship between collumns.

Relevent columns on the Master_Data sheet are A,B,C & F, and the area I'm looking at putting the formula is on the Breakdown sheet, cells highlighted in yellow.

As I said, I don't expect you to write the whole thing, but if you could take a look and see a way I could do what I'm after, I'd be extremely grateful.

Grateful as always,

=I=

EDIT: It keeps coming up with a database error when I try and upload this attachment, won't be a sec.

**DonkeyOte** · 02-27-2009, 07:03 AM

No attachment.

**Iscariot** · 02-27-2009, 07:10 AM

It's not having it, have refreshed and such repeatedly and tried attaching to a reply rather than editing in, it's just contactly having error messages. It's small enough so I assume it's a site problem at the moment. If you PM me your email address I can email you the sample, or I can try again later, whatever is best for you really Donkey.