Sumproduct Help

BrianM

Well-known Member
Joined
Jan 15, 2003
Messages
764
Could anyone explain why the formula in G16 does not work, but the one in G15 does... Thanks
Book3.xls
ABCDEFG
8EMPLOYEEIDACCRUALACCRUALTRANTYPENAMEExpr1EFFECTIVEDATEBalanceCall In Vac
94444Call In VacGRANT51/1/2005 
104444PersonalGRANT401/1/2005 
114444PersonalTAKEN-81/6/2005 
124444School LeaveGRANT81/1/2005 
134444VacationGRANT1841/1/2005 
144444VacationTAKEN-81/24/2005 
154444 Total2/11/25305
164445Call In VacGRANT51/1/200510
174445PersonalGRANT401/1/2005 
184445School LeaveGRANT81/1/2005 
194445VacationGRANT1841/1/2005 
204445WildCardGRANT82/4/2005 
214445 Total2/14/2425 
Sheet1
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,994
The -- before your B column criterion (both formulas) should probably have a comma before it, and you may need Value around the left() command in the first criterion

As such, I think G15 should =IF(ISBLANK(B15),SUMPRODUCT(--(A9:A15=VALUE(LEFT(A15,4))),--(B9:B15=$G$8),--(C9:C15="GRANT"),D9:D15))

And G16 should =IF(ISBLANK(B15),SUMPRODUCT(--(A9:A21=VALUE(LEFT(A16,4))),--(B9:B21=$G$8),--(C9:C21="GRANT"),D9:D21))
 

BrianM

Well-known Member
Joined
Jan 15, 2003
Messages
764
Ok, I see the typo, however. My answer should be 5, not 10.
When using A9:A15=LEFT(A15,4))--(B9:B15=$G$8),--(C9:C15="GRANT"),D9:D15 I get 5, which is correct, however if I increase the range it adds 5.
 

BrianM

Well-known Member
Joined
Jan 15, 2003
Messages
764

ADVERTISEMENT

Ahh, Thanks Oaktree, that's what I was doing wrong.

Thank you both for your help

Brian
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
These both give an answer of 5. There seemed to be a comma missing and also the left bit on col A was returning false for all records, which doing a *1 on sorts (text to val)

In G15 =IF(ISBLANK(B15),SUMPRODUCT(--(A9:A15=LEFT(A15,4)*1),--(B9:B15=$G$8),--(C9:C15="GRANT"),D9:D15))

and G16 =IF(ISBLANK(B15),SUMPRODUCT(--(A9:A21=LEFT(A16,4)*1),--(B9:B21=$G$8),--(C9:C21="GRANT"),D9:D21))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,870
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top