SUMIFs Formula Ranges Question

[Angelfish13]

Hi,

I have a table like below. I have a formula in another sheet that lets me know how many apples Bob bought on day 1 - it works great:

=SUMIFS(Sheet2!C:C,Sheet2!A:A,"Bob",Sheet2!B:B,"Apples")

BUT I need excel to sum the "month to date" as well to see how many apples Bob has bought so far this month....for instance finding the total for days columns 3-5. So I tried this:

=SUMIFS(Sheet2!C:E,Sheet2!A:A,"Bob",Sheet2!B:B,"Apples")

Now it returns a #VALUE ! error I think because there is a rule that the lookup formulas and ranges have to be the same size. Now I'm stumped. Are there any ways around this?

Follow-up question, not to complicate things.....but is there any way to make the summing of days "dynamic?" For instance, I need to run this report everyday to get the month to date (runs through day before). On Day 4, there will be three columns. But on day 26, there will be 25 columns. Do I have to change the formula every day to make the number of columns size right?


Please let me know, thanks so much for your help!
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Fruit Bought[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Apples[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Oranges[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Apples[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Sally[/TD]
[TD]Oranges[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Apples[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Oranges[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

SUMIFS cannot sum a multicolumn range.

Try rather: Control+shift+enter, not just enter...

=SUM(IF(Sheet2!A2:A4000="Bob",IF(Sheet2!B2:B4000="Apples",Sheet2!C2:E4000)))

 

[MARZIOTULLIO]

=SUMPRODUCT((Sheet2!$A$2:$A$4000="Bob")*(Sheet2!$B$2:$B$4000="Apples")*(Sheet2!$C$2:$E$4000))

 

[Aladin Akyurek]

=SUMPRODUCT((Sheet2!$A$2:$A$4000="Bob")*(Sheet2!$B$2:$B$4000="Apples")*(Sheet2!$C$2:$E$4000))

Try in C2 the following:

X

or

=""

 

[C with no eyes]

Insert a new column C in Sheet2 so your day1 data starts from D

C2 is =A2&B2 and drag down

This concatenates your two conditions into a unique value. (You can hide this column)

On your other sheet assuming A2 is Bob and B2 is Apples

=SUMIF(Sheet2!C:C,A2&B2,Sheet2!D:AH)

Please note AH would be day 31. As long as future days are not populated, this would work.

 

[Angelfish13]

Hi everyone,

I was finally able to figure this out! Thank you so much for the responses - you have saved me countless hours of work. I really appreciate your help!