Multiple Sum Ifs by month

[1Stacy]

Hi All,
Some of you may recall I had this problem with quarterly data recently.
i thought I'd be able to copy it monthly but no such luck - please help I'm not sure what i am doing wrong?

I have some criteria within my data entry (Budget Entry) tab and I'd like to sum all of the totals.
If the subproject code (7 digits) begins with 230 or 280 AND the month is 4 I'd like all entries to sum under April.
or quarterly data I was using Sumproduct and then a ROUNDUP/3 function to get teh data to display in quarters - this works perfectly, but it is not transferrably to months - should i use SUMIFS?

For quarters I am using:
SUMPRODUCT('Budget Entry'!$Q$2:$Q$200*((LEFT('Budget Entry'!$F$2:$F$200,3)="230")+(LEFT('Budget Entry'!$F$2:$F$200,3)="280"))*(ROUNDUP('Budget Entry'!$N$2:$N$200/3,0)=COLUMNS($H7:I7)))

[ConneXionLost]

Your sample data are not in the columns suggested in your formula; however, try this formula in cell J7 of your dashboard:

Please Login or Register  to view this content.

Note - Your sample data has no values where the subproject code starts with 230, and only one starting with 280. I.e. you'll still see mostly zeros.

Cheers,

[1Stacy]

Thanks, but i am not sure why you say =MONTH(DATEVALUE(J$4&"1"))
In my actual workbook i am puttin month (4) in cell W3 and Apr in cell W4 - if that helps.

As for the 0's I was just giving a sample with dummy data, but I'll keep that in mind.

thank you so much for your help!!!

[ConneXionLost]

In my actual workbook i am puttin month (4) in cell W3 and Apr in cell W4 - if that helps.

Yes it does. In that case, in place of =MONTH(DATEVALUE(J$4&"1")), just use =J$3, or on your actual workbook, =W$3.

Cheers,

[1Stacy]

That worked great ConneXionLost just one more thing, i wanted to add one more variable *('Budget Entry'!$A$3:$A$200=A5) but when I add it to the above formula it does not work.
The variable I am trying to add is Site.

[ConneXionLost]

What do you mean by "it does not work"? What result do you get?

[1Stacy]

The box that says The formula you have typed contains an error.

Thanks for all of your help - I am new to sumproduct and do not understand exactly how it works.

[ConneXionLost]

No worries, SUMPRODUCT is a challenging formula to work with. Can you post the formula you typed?

[1Stacy]

=SUMPRODUCT('Budget Entry 16 17'!$P$3:$P$200*('Budget Entry 16 17'!$B$3$B$200=A5)((LEFT('Budget Entry 16 17'!$G$3:$G$200,3)="230")+(LEFT('Budget Entry 16 17'!$G$3:$G$200,3)="280"))*('Budget Entry 16 17'!$O$3:$O$200=B$2))

My actual tab is called Budget Entry 16 17 and the tab that A5 is referencing is in the tab Infrastructure (which is the same tab that I am trying to display this information)
Budget Entry column B has the Site names and Infrastructure column A also has the site names that I'd like to match.

I think I've got it!
=SUMPRODUCT('Budget Entry 16 17'!$P$3:$P$200*('Budget Entry 16 17'!$B$3:$B$200=A5)*((LEFT('Budget Entry 16 17'!$G$3:$G$200,3)="230")+(LEFT('Budget Entry 16 17'!$G$3:$G$200,3)="280"))*('Budget Entry 16 17'!$O$3:$O$200=B$2))
It seems to be returning the right values.
Thanks for your link on SUMPRODUCT!

[ConneXionLost]

Great! Enjoy the link.

Cheers,