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
|