Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Shorten sumproduct formula
Hello,
Is there a way to use a sumproduct formula looking at non-contiguous cells? In cell a1, I have this formula: =SUMPRODUCT((G14<G15)*(H14G15)*(LEN(G14)0))+SUMP RODUCT((G16<G15)*(H16G15) *(LEN(G16)0)) I tried the following with no success: SUMPRODUCT(((G14,G16)<G15)*((H14,H16)G15)*(LEN((G 14,G16))0), but gives me a #VALUE Any suggestions? Thanks André |
#2
|
|||
|
|||
André,
Not sure why you need to shorten it, but is this any good (not the way you were thinking) =((G14<G15)*(H14G15)*(LEN(G14)0))+((G16<G15)*(H1 6G15)*(LEN(G16)0)) -- HTH RP (remove nothere from the email address if mailing direct) "Andre Croteau" wrote in message ... Hello, Is there a way to use a sumproduct formula looking at non-contiguous cells? In cell a1, I have this formula: =SUMPRODUCT((G14<G15)*(H14G15)*(LEN(G14)0))+SUMP RODUCT((G16<G15)*(H16G15) *(LEN(G16)0)) I tried the following with no success: SUMPRODUCT(((G14,G16)<G15)*((H14,H16)G15)*(LEN((G 14,G16))0), but gives me a #VALUE Any suggestions? Thanks André |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the results of a formula to show in cell, NOT THE FORMULA! | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) | |||
Paste is is copying in formula, but display is wrong. | Excel Discussion (Misc queries) | |||
What instead of an array formula? | Excel Discussion (Misc queries) | |||
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? | Excel Discussion (Misc queries) |