Conditional Sum

[slow386]

Hi Folks:
Using Excel 2003 - SP2
I have a simple 4 column table as below.
Model Number Type Series Qty

The Model Number is always a mix of letters and numbers and can vary in
length from 7 to 20 characters.
Model Numbers always start with 2 letters but can end with either a letter
or a number.
Certain model numbers can end with -N11, or -N12, etc., and what I would
like to count the total quanity of only those rows that end with -Nxx

I have tried
=SUM(IF(BE5:BH19="-N1*",BH5:BH19,1))
as well as
=SUM(IF(LEN(BE5:BE18)-LEN(SUBSTITUTE(BH5:BH18,"N1*",""))>1,1,1))
with no luck

I have also tried variants of "N*", "-N1*" with no luck
The range is actually BE5 to BI78 - just using smaller range to try and get
proper formula.

Any help sincerely appreciated
Thanks
Steve

 

[Peo Sjoblom]

=COUNTIF(BE5:BH19,"*-N??")


--


Regards,


Peo Sjoblom

 

[slow386]

Peo:
Thanks very much, but it's only counting the instances of "N"
What I need it to do is when it sees an instance of "N" in BE, go to BH and
add those qtys
Thanks very much.

Steve

 

[Peo Sjoblom]

Is this what you want?

=SUMPRODUCT(--(ISNUMBER(FIND("-N",BE5:BE19))),BH5:BH19)


--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

Doh!

Use this instead

=SUMIF(BE5:BE19,"*-N??",BH5:BH19)

just in case there can be more than 2 characters after N


--


Regards,


Peo Sjoblom

 

[slow386]

You are THE MAN !!!!
Many many thanks

Steve

Is this what you want?

=SUMPRODUCT(--(ISNUMBER(FIND("-N",BE5:BE19))),BH5:BH19)


Regards,


Peo Sjoblom