Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT QUERY
Hi
I have a spreadsheet where a formulae '=SUMPRODUCT((A$2:A$11="S")*(A$2:A$11<A$3:A$12)) ' was initially set to count separate instances of "S"in a Column A S SD SD SN S This works fine but I need to extend the formula so it counts the number of instances of S, SD or SN. In the above example there are four seperate instances but the formula I have created ' =SUMPRODUCT((A$2:A$11="S")*(A$2:A$11<A$3:A$12))+S UMPRODUCT((A$2:A$11="SD")* (A$2:A$11<A$3:A$12))+SUMPRODUCT((A$2:A$11="SN")*( A$2:A$11<A$3:A$12)) 'counts five because although the SD and SN follow each other it counts them as seperate. I need it to count the SD & SN together as one instance. Hope I have explained OK - any suggestions welcome. Regards Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT QUERY
Paul,
Want to give this a whirl =SUMPRODUCT((LEFT(A$2:A$11,1)="S")*(LEFT(A$2:A$11, 1)<(LEFT(A$3:A$12,1)))) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Paul Bird" wrote in message ... Hi I have a spreadsheet where a formulae '=SUMPRODUCT((A$2:A$11="S")*(A$2:A$11<A$3:A$12)) ' was initially set to count separate instances of "S"in a Column A S SD SD SN S This works fine but I need to extend the formula so it counts the number of instances of S, SD or SN. In the above example there are four seperate instances but the formula I have created ' =SUMPRODUCT((A$2:A$11="S")*(A$2:A$11<A$3:A$12))+S UMPRODUCT((A$2:A$11="SD")* (A$2:A$11<A$3:A$12))+SUMPRODUCT((A$2:A$11="SN")*( A$2:A$11<A$3:A$12)) 'counts five because although the SD and SN follow each other it counts them as seperate. I need it to count the SD & SN together as one instance. Hope I have explained OK - any suggestions welcome. Regards Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUMPRODUCT QUERY
Thanks Bob - did the trick straight away - very useful.
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct query | Excel Worksheet Functions | |||
SUMPRODUCT QUERY | Excel Worksheet Functions | |||
Sumproduct query | Excel Worksheet Functions | |||
SUMPRODUCT IF query | Excel Discussion (Misc queries) | |||
Sumproduct Query | Excel Discussion (Misc queries) |