| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Sum | New Users to Excel | |||
| Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
| Conditional Min Value | Excel Discussion (Misc queries) | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
| Conditional Sum by Row | Excel Worksheet Functions | |||
Linear Mode
