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

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

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!