Sumproduct Help

[BrianM]

Could anyone explain why the formula in G16 does not work, but the one in G15 does... Thanks

Book3.xls
	A	B	C	D	E	F	G
8	EMPLOYEEID	ACCRUAL	ACCRUALTRANTYPENAME	Expr1	EFFECTIVEDATE	Balance	Call In Vac
9	4444	Call In Vac	GRANT	5	1/1/2005
10	4444	Personal	GRANT	40	1/1/2005
11	4444	Personal	TAKEN	-8	1/6/2005
12	4444	School Leave	GRANT	8	1/1/2005
13	4444	Vacation	GRANT	184	1/1/2005
14	4444	Vacation	TAKEN	-8	1/24/2005
15	4444 Total				2/11/2530		5
16	4445	Call In Vac	GRANT	5	1/1/2005		10
17	4445	Personal	GRANT	40	1/1/2005
18	4445	School Leave	GRANT	8	1/1/2005
19	4445	Vacation	GRANT	184	1/1/2005
20	4445	WildCard	GRANT	8	2/4/2005
21	4445 Total				2/14/2425
Sheet1

 

[Scott Huish]

EDIT: Post deleted.

 

[Oaktree]

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]

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]

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

Thank you both for your help

Brian

 

[GorD]

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))

 

[GorD]

(Much) Too slow with that one