Multiple Sums - SUMIFS? SUMPRODUCT?

I seem to be missing a few pieces here. Are you trying to sum up the data from the budget entry page? If so, how are you getting any numbers for April since there is no data for April? Also how do you know what the capital items are? Is there some sort of table that connects subproject with the breakout into capital item?

The solution I would pick would be SUMPRODUCT because you can use wildcards (SUMIFS will let you do this too) but you have a bit more flexibility in the logic. It turns out that your data is numeric so wildcards wont work. Given the data you do have I concocted a formula:

=SUMPRODUCT((N3:N25)*(M3:M25=11)*( (INT(E3:E25/100000)=27 ) + (INT(E3:E25/100000)=21 )))

With SUMPRODUCT multiplication (*) is like an AND statement and addition (+) is like an OR statement. So the above reads:

Sum the quantity where month is 11 and (code starts with 21 or 27).

SUMPRODUCT is an array formula in disguise even though it is not entered like one, so you can get away with doing operations on ranges like INT(Range/100000). It's as if you took each cell in the range and applied the formula to it.

Originally Posted by 1Stacy

Are you able to tell me what this is asking:
( (INT(E3:E25/100000)=27 ) + (INT(E3:E25/100000)=21 )))

The INT(E3:E25/100000) takes each cell in the range E3:E25 and divides it by 100,000 and then takes whatever the integer part is. For example if the cell contained 2712345, it would be converted to 27.12345 and INT(27.12345) is 27.

The formula doesn't actually change the cell contents, but it holds the computations in an array internally.

I suppose I could also have used LEFT(E3:E25,2).

As for the + and * operators. * acts like AND and + works like OR. So suppose we manufactured balloons of various colors and in three sizes and we wanted to know how many large red balloons and large blue balloons we sold.

You could use =SUMPRODUCT ((Size="Large")*(Color="RED")) + SUMPRODUCT ((Size="Large")*(Color="BLUE"))
or you could use
=SUMPRODCUT ((Size="Large") * ( (Color="RED") + (Color="BLUE") ) )

To get a better idea how array arithmetic works, see this article: http://www.utteraccess.com/wiki/inde...Array_Formulas.

I am having trouble with one of the other formulas - I am still trying to do the same thing, except this time I want to use the Left(G2:G600,3)="210" + Left(G2:G600, 3)="220" but it does not seem to be working
I have a lot of SubProject codes that begin with 210 or 220 that will be used for this cost centre, will INT still work?
I am trying:
=SUMPRODUCT(('Budget Entry 16 17'!$R$2:$R$600)*('Budget Entry 16 17'!$O$2:$O$600=4))*((LEFT('Budget Entry 16 17'!$G$2:$G$600,3)="210"))+(LEFT('Budget Entry 16 17'!$G$2:$G$600,3)="220")
OK so:
=SUMPRODUCT(('Budget Entry 16 17'!$R$2:$R$600)*('Budget Entry 16 17'!$O$2:$O$600=4)*((INT('Budget Entry 16 17'!$G$2:$G$600/100000)=22 )+(INT('Budget Entry 16 17'!$G$2:$G$600/100000)=21)))
Worked!

dflak - thanks for all of your help, I just like to post the answer that worked for others who might be reading or searching this can see what worked in the end!