kgerb
asked on
SUMPRODUCT with Non-Contiguous Range
Hello All,
I've been struggling with this for quite a while now and I can't seem to get it right. Here is what I need to do:
=SUMPRODUCT((K11,O11,S11,W 11,AA11,AE 11,AI11,AM 11,AQ11)=" R",(H11,L1 1,P11,T11, X11,AB11,A F11,AJ11,A N11))
I realize this doesn't work, but you get the idea. Check whether the cells K11, O11, S11, ... are equal to "R" and create an array with 1's and 0's. Multiply this array with another array created from the values of the cells H11, L11, P11, ... and then sum the result.
I tried the following and I think it should work but it's not and I don't know why. I don't really like this approach b/c the cell references are static strings and I can't drag the formula down but if I have to use something like this I will.
=SUMPRODUCT(INDIRECT({"K11 ","O11","S 11","W11", "AA11","AE 11","AI11" ,"AM11","A Q11"})="R" ,INDIRECT( {"H11","L1 1","P11"," T11","X11" ,"AB11","A F11","AJ11 ","AN11"}) )
Thanks in advance for the help.
Kyle
I've been struggling with this for quite a while now and I can't seem to get it right. Here is what I need to do:
=SUMPRODUCT((K11,O11,S11,W
I realize this doesn't work, but you get the idea. Check whether the cells K11, O11, S11, ... are equal to "R" and create an array with 1's and 0's. Multiply this array with another array created from the values of the cells H11, L11, P11, ... and then sum the result.
I tried the following and I think it should work but it's not and I don't know why. I don't really like this approach b/c the cell references are static strings and I can't drag the formula down but if I have to use something like this I will.
=SUMPRODUCT(INDIRECT({"K11
Thanks in advance for the help.
Kyle
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad to help. :)
ASKER
Thanks Rory
Kyle