Sumproduct with Vlookup

[cwallace70]

I have a summary sheet with 12 monthly worksheets that I want a sum of the following. It contains 3 criterias.
The summary sheets have a dept code of "EN" and I want a sum from all 12 monthly worksheets that have that same code in column C with an amount in column E.
Ca nthis be done with sumproduct and vlookup or just sumproduct?

 

[T. Valko]

For the same spreadsheet you assisted me with, I am trying to create another formula, but I keep getting zero's can you please look at this formula and tell me what I am doing wrong

I want a sum for product code UL for the agency in A7 from all monthly tabs. Jan to Dec are A101 - L101
product code UL is column D. The amounts are in column E, F, & G.
So in essence I want the total thta pertains to agency 46without hardcoding it so it would be $A7 and sum all amounts in columns EFG that have a product code of "UL".

=SUMPRODUCT(SUMIF(INDIRECT($A$101:$L$101&"!C:C"),$A7*(SUMIF(INDIRECT($A$101:$L$101&"!D:D"),UL,INDIRECT($A$101:$L$101&"!E:E")))))


I have tried many different formulas and getting error messages nad zeros.

Thanks in advance for your assistance.

Some functions do not work well for 3D referencing. A signle conditional sum or count isn't too bad but a multiple conditional sum or count is extremely complicated and very calculation intensive.

I would recommend putting a formula on each sheet in the same cell then doing a simple SUM of that cell across the sheets.

For example, if I understand what needs to be summed...

On each sheet in the same cell, say cell A1:

=SUMPRODUCT(--(C5:C100=A7),--(D5:D100="UL"),E5:E100)

Then, on your summary sheet you'd sum cell A1 on all the monthly sheets:

=SUM(Jan:Dec!A1)

 

[cwallace70]

=SUMPRODUCT(--($C$6:$C$59=$N12),--($D$6:$D$59="L99")+($E$6:$E$59)+($F$6:$F$59)+($G$6:$G$59))

Can you please help me with this formula. I am trying to get a sum for the agency in column N12 that have a code of L99 in column D and add all the amounts in column E, F & G that pertains to it.

The problem I am running into is this formula is adding everything in columns E,F & G even if it does not have the "L99" code.
I hope I explained my self correctly. Thanks

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H
1	Criteria	AA						SumProduct 2 criteria for multiple columns
2		L99	134	134	134
3			AA		24	40	56
4			AA	L99	26	42	66
5			BB	L99	28	44	76
6			CC		30	46	86
7			CC	L99	32	48	96
1b

Excel 2003

Cell Formulas
Range	Formula
C2	=SUMPRODUCT((C3:C7=B1)*(D3:D7=B2)*(E3:G7))
D2	=SUMPRODUCT(--(C3:C7=B1),--(D3:D7=B2),(E3:E7+F3:F7+G3:G7))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

I suggest that you build a very small table that you can experiment with.

You can review the formulas with Excel's "Evaluate Formula".

 

[T. Valko]

=SUMPRODUCT(--($C$6:$C$59=$N12),--($D$6:$D$59="L99")+($E$6:$E$59)+($F$6:$F$59)+($G$6:$G$59))

Can you please help me with this formula. I am trying to get a sum for the agency in column N12 that have a code of L99 in column D and add all the amounts in column E, F & G that pertains to it.

The problem I am running into is this formula is adding everything in columns E,F & G even if it does not have the "L99" code.
I hope I explained my self correctly. Thanks

Try it like this...

=SUMPRODUCT(--($C$6:$C$59=$N12),--($D$6:$D$59="L99"),$E$6:$E$59+$F$6:$F$59+$G$6:$G$59)

 

[cwallace70]

=SUMPRODUCT(--($C$6:$C$59=$N16),--($D$6:$D$59=S$4),$E$6:$E$59+$F$6:$F$59+$G$6:$G$59)

The formula above worked to sum on the individual monthly tabs. Thank You

but I am using this formula to pull a sum to the sumary sheet and I am getting the REF error message.

=SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!N:N"),$A10,INDIRECT($A104:$L104&"!R:R+AE:AE")))

I need to sum morethan one column on the monthly tab.

 

[T. Valko]

=SUMPRODUCT(--($C$6:$C$59=$N16),--($D$6:$D$59=S$4),$E$6:$E$59+$F$6:$F$59+$G$6:$G$59)

The formula above worked to sum on the individual monthly tabs. Thank You

but I am using this formula to pull a sum to the sumary sheet and I am getting the REF error message.

=SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!N:N"),$A10,INDIRECT($A104:$L104&"!R:R+AE:AE")))

I need to sum morethan one column on the monthly tab.

That particular formula won't work across sheets.

You would have to do something like this:

=SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!N:N"),$A10,INDIRECT($A104:$L104&"!R:R")))+SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!N:N"),$A10,INDIRECT($A104:$L104&"!AE:AE")))

 

[Aladin Akyurek]

=SUMPRODUCT(--($C$6:$C$59=$N16),--($D$6:$D$59=S$4),$E$6:$E$59+$F$6:$F$59+$G$6:$G$59)

The formula above worked to sum on the individual monthly tabs. Thank You

Instead of mixing vectors and matrices within a SumProduct, you can also invoke:

=SUM(
   IF($C$6:$C$59=$N16,
   IF($D$6:$D$59=S$4,
     $E$6:$G$59)))

which needs to be confirmed with control+shift+enter, not just with enter.

but I am using this formula to pull a sum to the sumary sheet and I am getting the REF error message.

=SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!N:N"),$A10,INDIRECT($A104:$L104&"!R:R+AE:AE")))

I need to sum morethan one column on the monthly tab.

SumIf cannot total a matrix sum-range or multiple non-contiguous areas.

Also an option to consider...

Let Sheet1 house $A10, the criterion value.

Create a summary area on each sheet listed in $A104:$L104, with the following formula:

A2, control+shift+enter, not just enter, and copy down:

=SUM(IF(N:N=Sheet1!$A10,R:R+AE:AE))

In B10 on Sheet1 enter:

=SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!A2:A20"),">0"))

 

[cwallace70]

Forgive me but I do not have a clue as to what your suggesting.....Is it possible to exclude SUMIF and just use SUMPRODUCT TO CREAT THAT TYP OF FORMULA.
When I use this formula
=SUMPRODUCT(SUMIF(INDIRECT($A$99:$L$99&"!N:N"),$A41,INDIRECT($A$99:$L$99&"!R:R")))
I am getting the correct sum..so is ther a way to include another column into the summary. for example AE:AE.

I also would like to eliminate adding a 2nd summary to each monthly tab.

 

[T. Valko]

Forgive me but I do not have a clue as to what your suggesting.....Is it possible to exclude SUMIF and just use SUMPRODUCT TO CREAT THAT TYP OF FORMULA.

It's possible but it's extremely complicated and the resulting formula is very long and inefficent. I recommend NOT going that route.

When I use this formula
=SUMPRODUCT(SUMIF(INDIRECT($A$99:$L$99&"!N:N"),$A41,INDIRECT($A$99:$L$99&"!R:R")))
I am getting the correct sum..so is ther a way to include another column into the summary. for example AE:AE.
I also would like to eliminate adding a 2nd summary to each monthly tab.

You would have to do it as I showed in my other reply.

 

[cwallace70]

Instead of mixing vectors and matrices within a SumProduct, you can also invoke:

=SUM(
   IF($C$6:$C$59=$N16,
   IF($D$6:$D$59=S$4,
     $E$6:$G$59)))

which needs to be confirmed with control+shift+enter, not just with enter.



SumIf cannot total a matrix sum-range or multiple non-contiguous areas.

Also an option to consider...

Let Sheet1 house $A10, the criterion value.

Create a summary area on each sheet listed in $A104:$L104, with the following formula:

A2, control+shift+enter, not just enter, and copy down:

=SUM(IF(N:N=Sheet1!$A10,R:R+AE:AE))

In B10 on Sheet1 enter:

=SUMPRODUCT(SUMIF(INDIRECT($A104:$L104&"!A2:A20"),">0"))

Okay, can you explain in more detail these steps to me