Sumproduct

[1Stacy]

Hi All,
Still trying to wrap my head around SUMPRODUCT.

I have written (with a lot of help from the Forum!) SUMPRODUCT formulas where the column Subproject on my data sheet begins with 270 or ends with 3380, but now I am trying to write one where the Subproject is 2703380 and the month is 4 (5,6,7...)

I have tried:
SUMPRODUCT((('Budget Entry 16 17'!R2:R600),('Budget Entry 16 17'!G2:G600)="2703880")*('Budget Entry 16 17'!O2:O600)="4")
But I get a VALUE

[tigeravatar]

In this case, you are getting a #VALUE! error because you are multiplying text. You have your range set to start at row 2, but in the provided file row 2 is a header row, so you are trying to multiply headers together. There are also several syntax problems in the formula, so I'm kind of surprised Excel even took the formula without giving you an error. Finally, this is a .xlsx file which means you have Excel 2007 or higher, so maybe you want SUMIFS instead of SUMPRODUCT?

Using your provided sample file, and assuming you want the following:
Sum the Amount Paid (column R) IF the SubProject (column G) = 2703380 AND the Accounting Month (column O) = 4

Then the formula you want is:

Please Login or Register  to view this content.

However, note that in your provided sample file, there are no rows in the 'Budget Entry' sheet in which the SubProject is 2703380 and the Accounting Month is 4, so this formula results in a 0. You can verify it works by changing the 4 to a different account month and confirm it sums properly for that SubProject.

Lastly, if you want to add multiple accounting months for a single subproject, you would indeed bring Sumproduct back into the mix, like so:

Please Login or Register  to view this content.

[1Stacy]

Awesome thanks! SumIfs worked.
I ended up using:
=SUMIFS('Budget Entry 16 17'!$R:$R,'Budget Entry 16 17'!$G:$G,2703380,'Budget Entry 16 17'!$O:$O,4)

[tigeravatar]

You're very welcome