sumif: multiple critera including date

**his5r2m** · 05-20-2009, 10:54 AM

hello,

I’m trying to get the sum of a column, with specific criteria.

In cell 3B, in the "asbestos" sheet, I would like the sum of column H in the "incidents" sheet matching the criteria:

Incidents sheet:
Column A = asbestos
Column B = January

The code I’ve tried is:

((Incidents!$A$4:$A$500=$a$1)*(Incidents!$C$4:$C$500>=$A4)*(Incidents!$C$4:$C$500<=DATE(YEAR($A4),MONTH($A4)+1,1))

I’m sure there’s really simple solution to this...

Any help much appreciated!

many thanks

Rich

**DonkeyOte** · 05-20-2009, 10:57 AM

Rich, the values in A are not dates.

Also per your other (very similar) thread note my point in post # 7 re: C56 being a text value rather than a date.

Assuming you correct the above:

=SUMPRODUCT((Incidents!$A$4:$A$500=$A$1)*(Incidents!$C$4:$C$500>=$A3)*(Incidents!$C$4:$C$500<=DATE(YEAR($A3),MONTH($A3)+1,1)))

Though again as mentioned before if running XL07 you should use COUNTIFS (assuming no backwards compatibility concerns)

**his5r2m** · 05-20-2009, 11:06 AM

Last time I was counting instances when a column had specific entry: i.e. how many accident codes in a given month. This time I need a formula that gives me the total of the contents of column h: the sum number of days lost in a given month.

I don’t need how many times a day is lost, I need how many days in total, in "asbestos" dept, in January.

I was hoping that might be just about different enough to merit a new thread?

apologies to all if not.

Rich

**DonkeyOte** · 05-20-2009, 11:08 AM

Adapting the post#2 formula re:H

=SUMPRODUCT((Incidents!$A$4:$A$500=$A$1)*(Incidents!$C$4:$C$500>=$A3)*(Incidents!$C$4:$C$500<=DATE(YEAR($A3),MONTH($A3)+1,1))*(Incidents!$H$4:$H$500))

I myself prefer the double unary approach which is marginally quicker:

=SUMPRODUCT(--(Incidents!$A$4:$A$500=$A$1),--(Incidents!$C$4:$C$500>=$A3),--(Incidents!$C$4:$C$500<=DATE(YEAR($A3),MONTH($A3)+1,1)),Incidents!$H$4:$H$500)

See the link in my sig for more info on Sumproduct.

In XL07 you would use SUMIFS in this instance (significantly quicker):

=SUMIFS(Incidents!$H$4:$H$500,Incidents!$A$4:$A$500,$A$1,Incidents!$C$4:$C$500,">="&$A3,Incidents!$C$4:$C$500,"<="&DATE(YEAR($A3),MONTH($A3)+1,1))

sumif: multiple critera including date

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

sumif: multiple critera including date

Re: sumif: multiple critera including date

Re: sumif: multiple critera including date

Re: sumif: multiple critera including date

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions