Conditional Sum
 

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

Conditional Sum
 

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


--


Regards,


Peo Sjoblom



"slow386" wrote in message
...
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

Conditional Sum
 

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" wrote in message
...
=COUNTIF(BE5:BH19,"*-N??")

Regards,


Peo Sjoblom



"slow386" wrote in message
...
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

Conditional Sum
 

Is this what you want?

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


--


Regards,


Peo Sjoblom


"slow386" wrote in message
...
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" wrote in message
...
=COUNTIF(BE5:BH19,"*-N??")

Regards,


Peo Sjoblom



"slow386" wrote in message
...
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

Conditional Sum
 

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


"Peo Sjoblom" wrote in message
...
Is this what you want?

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


--


Regards,


Peo Sjoblom


"slow386" wrote in message
...
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" wrote in message
...
=COUNTIF(BE5:BH19,"*-N??")

Regards,


Peo Sjoblom



"slow386" wrote in message
...
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

Conditional Sum
 

You are THE MAN !!!!
Many many thanks

Steve

"Peo Sjoblom" wrote in message
...
Is this what you want?

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


Regards,


Peo Sjoblom


"slow386" wrote in message
...
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" wrote in message
...
=COUNTIF(BE5:BH19,"*-N??")

Regards,


Peo Sjoblom



"slow386" wrote in message
...
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