Simplefying a formula with multiple SUMIF

[20fleming]

Hi all,


I've got a SUMIF formula that works but will be complex to expand. Junt wondering if there is a easier way of doing this is excel 2007

A10= SUMIFS(b2:b4,a2:a4,">="&B6,a2:a4,"<="&C6)+SUMIFS(d2:d4,c2:c4,">="&B6,c2:c4,"<="&C6)
result would be count of dates beween Apr1 - Apr 30. total =9

B10= SUMIFS(b2:b4,a2:a4,">="&c6,a2:a4,"<="&d6)+SUMIFS(d2:d4,c2:c4,">="&c6,c2:c4,"<="&d6)
result would be count of dates between May1 - May31. total= 6

the date range in row 6 will go on for 48 months

[TABLE="width: 500"]
<TBODY>[TR]
[TD]date1[/TD]
[TD]count1[/TD]
[TD]date2[/TD]
[TD]count2[/TD]
[TD]date3[/TD]
[TD]count3[/TD]
[/TR]
[TR]
[TD]4/3/14[/TD]
[TD]2[/TD]
[TD]5/9/14[/TD]
[TD]1[/TD]
[TD]4/15/14[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5/8/14[/TD]
[TD]3[/TD]
[TD]4/23/14[/TD]
[TD]2[/TD]
[TD]5/19/14[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4/9/14[/TD]
[TD]1[/TD]
[TD]8/6/15[/TD]
[TD]1[/TD]
[TD]1/1/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]date range[/TD]
[TD]1/4/14[/TD]
[TD]1/5/14[/TD]
[TD]1/6/14[/TD]
[TD]1/7/14[/TD]
[TD]1/8/14[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Rijnsent]

Hi 20fleming,

can't you reshuffle your data, so it's all in one column? That would make your formula much easier...

Greets,

Koen

 

[barry houdini]

You should be able to use a single SUMIFS like this

=SUMIFS($b2:$z4,$a2:$y4,">="&B6,$a2:$y4,"<="&C6)

Notice how the ranges are "offset" by one column from each other. This works as long as the "counts" are relatively low values so that they can't be mistaken for dates

 

[20fleming]

Thank you both for your suggestions. I've put all of the data in 1 column on another tab and done a vlookup as I can't change the formatting. Barry, thanks for your suggestion. unfortunately my number do get into high values.

 

[markmzz]

If I understand correctly what you want, maybe this can helps:

=SUM(SUMIFS(OFFSET($B$2:$B$4,,2*{0,1,2}),OFFSET($A$2:$A$4,,2*{0,1,2}),">="&OFFSET($B$6,,2*(ROW($1:$24)-1)),OFFSET($A$2:$A$4,,2*{0,1,2}),"<="&OFFSET($C$6,,2*(ROW($1:$24)-1))))

Markmzz

 

[barry houdini]

Barry, thanks for your suggestion. unfortunately my number do get into high values.

OK, assuming you do really have headers that say "date1", "count1" etc. then this formula will do what you want, with no confusion possible between date and count values

=SUMPRODUCT(($a2:$y4>=B6)*($a2:$y4<=C6)*(LEFT($A1:$Y1,4)="date"),$B2:$z4)

extend ranges as required

 

[markmzz]

A small modification in my formula:

Use Ctrl+Shift+Enter and not only Enter to enter the formula

=SUM(SUMIFS(OFFSET($B$2:$B$4,,2*{0,1,2}),
OFFSET($A$2:$A$4,,2*{0,1,2}),">="&SUMIF(OFFSET($B$6,,ROW($1:$24)-1),"<>"),
OFFSET($A$2:$A$4,,2*{0,1,2}),"<"&SUMIF(OFFSET($C$6,,ROW($1:$24)-1),"<>")))

Markmzz