SumProduct Referencing Help

[1Stacy]

I'm trying to reference a 7 digit code where I really only need the first 2 digits.

Then sum all of the values with that figure - see attached.

If the SubProject (column G) begins with 28 or 23 I'd like to total the spend
If the SubProject begins with 22 or 21 I'd like to total the spend.

I do have a value of 2853530 which is entirely separate.

Apologies if this is a duplicate post from earlier today - what I thought I posted previously is not showing up under My Posts.

[JohnTopley]

In B2

=SUMPRODUCT(('Budget Entry 16 17'!$S$2:$S$12)*('Budget Entry 16 17'!$P$2:$P$12=MONTH(B$1&0))*(LEFT('Budget Entry 16 17'!$G$2:$G$12,3)={"230","280"}))

Copy across

in B3

=SUMPRODUCT(('Budget Entry 16 17'!$S$2:$S$12)*('Budget Entry 16 17'!$P$2:$P$12=MONTH(B$1&0))*(LEFT('Budget Entry 16 17'!$G$2:$G$12,3)={"210","220"}))

in B4

=SUMPRODUCT(('Budget Entry 16 17'!$S$2:$S$12)*('Budget Entry 16 17'!$P$2:$P$12=MONTH(B$1&0))*('Budget Entry 16 17'!$G$2:$G$12=2853530))


Remove spaces from codes OR change last formula to...

Please Login or Register  to view this content.