| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanded sumproduct
I have this and it works fine:
=SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
I may not be understanding what you want but try
=SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002) -- Don Guillett Microsoft MVP Excel SalesAid Software "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
What I am looking for is that the 1st function has sumproduct on three
different columns but the criteria is only one item. The 2nd function has one column but multiple criteria How do I make the formula so I can sum on multiple criteria in column A ; and then also include in the function columns B and C and their criteria. Thank you, Steven "Don Guillett" wrote: I may not be understanding what you want but try =SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002) -- Don Guillett Microsoft MVP Excel SalesAid Software "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Basically:
Why does this not work: =SUMPRODUCT((SUMIF($A$14:$A$30002,{"A","B"}))*($B$ 14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$3 0002)) where column D are the amounts. Thank you, Steven "Steven" wrote: What I am looking for is that the 1st function has sumproduct on three different columns but the criteria is only one item. The 2nd function has one column but multiple criteria How do I make the formula so I can sum on multiple criteria in column A ; and then also include in the function columns B and C and their criteria. Thank you, Steven "Don Guillett" wrote: I may not be understanding what you want but try =SUMPRODUCT(A14:A30002={"A","B","C"}),D14:D30002) -- Don Guillett Microsoft MVP Excel SalesAid Software "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
=SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3)
*($C$14:$C$30002=$C$2)*$D$14:$D$30002) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2) *($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Yes, Thank you very much.
"Ragdyer" wrote: =SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3) *($C$14:$C$30002=$C$2)*$D$14:$D$30002) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2) *($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
One last question:
Instead of {"A","B","C"} is there a way to use cell references? I have tried many things without sucess. Thank you, Steven "Steven" wrote: Yes, Thank you very much. "Ragdyer" wrote: =SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3) *($C$14:$C$30002=$C$2)*$D$14:$D$30002) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2) *($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Hi,
The only problem with this solution is you can't put cell references within {}. So if you want to use cell references here is one solution: =SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B$ 14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$3 0002)) Then the formula requires array entery - Shift+Ctrl+Enter If the reference cell B1:B3 are switched to B1:D1 (a row) then =SUMPRODUCT(($A$14:$A$30002=B1:D1)*($B$14:B$30002= $A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002)) without array entry. -- Thanks, Shane Devenshire "Steven" wrote: Yes, Thank you very much. "Ragdyer" wrote: =SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3) *($C$14:$C$30002=$C$2)*$D$14:$D$30002) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2) *($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Shane,
Thank you for your response. Is there a limit of using only one TRANSPOSE in the formula. I have tried with additional and I cannot make it work. Steven "ShaneDevenshire" wrote: Hi, The only problem with this solution is you can't put cell references within {}. So if you want to use cell references here is one solution: =SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B$ 14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$3 0002)) Then the formula requires array entery - Shift+Ctrl+Enter If the reference cell B1:B3 are switched to B1:D1 (a row) then =SUMPRODUCT(($A$14:$A$30002=B1:D1)*($B$14:B$30002= $A$3)*($C$14:$C$30002=$C$2)*($D$14:$D$30002)) without array entry. -- Thanks, Shane Devenshire "Steven" wrote: Yes, Thank you very much. "Ragdyer" wrote: =SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3) *($C$14:$C$30002=$C$2)*$D$14:$D$30002) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2) *($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
"ShaneDevenshire" wrote...
.... =SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B $14:B$30002=$A$3)* ($C$14:$C$30002=$C$2)*($D$14:$D$30002)) .... Or as long as A14:A30002 don't contain wildcard characters, =SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B $30002=$A$3)* ($C$14:$C$30002=$C$2)*($D$14:$D$30002)) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Try this ... needs only regular entry:
=SUMPRODUCT(($A$14:$A$30002=T(INDIRECT({"B1","B2", "B3"}))) *($B$14:B$30002=$A$3)*($C$14:$C$30002=$C$2)*$D$14: $D$30002) Assumes values in B1, B2, and B3 are Text. If they're numeric, change the "T" in front of Indirect to an "N". HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Steven" wrote in message ... One last question: Instead of {"A","B","C"} is there a way to use cell references? I have tried many things without sucess. Thank you, Steven "Steven" wrote: Yes, Thank you very much. "Ragdyer" wrote: =SUMPRODUCT(($A$14:$A$30002={"A","B","C"})*($B$14: B$30002=$A$3) *($C$14:$C$30002=$C$2)*$D$14:$D$30002) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Steven" wrote in message ... I have this and it works fine: =SUMPRODUCT(($A$14:$A$30002=$B$1)*($B$14:B$30002=$ A$3)*($C$14:$C$30002=$C$2) *($D$14:$D$30002)) And this works fine if I want more than one value from column A: =SUMPRODUCT(SUMIF(A14:A30002,{"A","B","C"},D14:D30 002)) But how do I include the the column B and C columns and criteria into the 2nd formula. Thank you, Steven |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Am I missing something here?
What's wrong with ISNUMBER(MATCH(...)) -- Biff Microsoft Excel MVP "Harlan Grove" wrote in message ... "ShaneDevenshire" wrote... ... =SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($ B$14:B$30002=$A$3)* ($C$14:$C$30002=$C$2)*($D$14:$D$30002)) ... Or as long as A14:A30002 don't contain wildcard characters, =SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B $30002=$A$3)* ($C$14:$C$30002=$C$2)*($D$14:$D$30002)) |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Expanded sumproduct
Harland,
The countif is a WOW. Thank you, Steven "Harlan Grove" wrote: "ShaneDevenshire" wrote... .... =SUMPRODUCT(($A$14:$A$30002=TRANSPOSE(B1:B3))*($B $14:B$30002=$A$3)* ($C$14:$C$30002=$C$2)*($D$14:$D$30002)) .... Or as long as A14:A30002 don't contain wildcard characters, =SUMPRODUCT(COUNTIF(B1:B3,$A$14:$A$30002)*($B$14:B $30002=$A$3)* ($C$14:$C$30002=$C$2)*($D$14:$D$30002)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Expanded Vlookup | Excel Discussion (Misc queries) | |||
| Can the function window be expanded? | Excel Worksheet Functions | |||
| Can worksheet space be expanded | Excel Discussion (Misc queries) | |||
| SUMPRODUCT formula expanded from original | Excel Discussion (Misc queries) | |||
| Tab Key Expanded Cell Movement | Excel Discussion (Misc queries) | |||
Linear Mode
