i have a workbook that im trying to make...almost done thank god...trying to figure one thing out though.
i have this formula : =COUNTIF(line," solo")
what i would like to have this do is to check a singular cell for multiple words
example:
g4 contains the word "solo"
g5 contains the word "dsl/voice"
now "solo" would count as 1 unit and "dsl/voice" would count as 2 units
How can i have this formula do this so it searches each word but only counts it if it is present? also if it contains the "dsl/voice" to times that by 2?
Each month these lines change.
I hope thats clear hehe
Thanks for all the help guys
dont put stuff in one cell! is the recommended way
but a bigger example of data would be a help ahat exactly are you counting?
you could just see if there is a ? and multiply by 2 eg =COUNTIF(A1:A10,"*/*")
Last edited by martindwilson; 12-28-2009 at 03:41 PM.
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
i have a spreadsheet with sales info
solo
dsl/voice
voice
cable
now all but dsl/voice count as 1 unit
dsl/voice count as 2
each row is a sale... with info on that sale
username customername date linetype salesperson units
A1 davidjmor David Morin 12/28/09 dsl/voice sboud 2
A2 testuser test guy 12/28/09 solo sboud 1
above is what the row looks like, the linetype is what i want to count....so the username davidjmor would count as 2 units and the testuser would count as 1 unit. now A1 next month might be a "solo" so it would count as 1. how can i change the "units" column to count that cell to see what it is and then apply appropriate number there.
Are there line types with more than a single "/" in the text eg could you have "solo/dsl/voice" or is it a case of either zero "/" or 1 "/"?
If it's the latter case (i.e. a maximum of 1 "/" in the linetype) , you could use something like the following formula into the required cell:
and copy down.Code:=IF(ISERROR(FIND("/",g4,1)),1,2)
this will enter a 1 if there is no "/" in the linetype. Otherwise it will enter 2.
Excel 2003 user (and starting to warm to Excel 2007)
=IF(ISERROR(FIND("/",G4,1)),1,2)
That worked perfect.......Thank you so much!!!!
You're welcome- make sure to mark your post as solved (Go to your original post to do that).
Excel 2003 user (and starting to warm to Excel 2007)
one last thing...i hate to be a bother but is there a way to make it so that if cell is empty to display 0 or nothing at all?
should do it for blank cells. Substitute alternative text in quotes (for the red zero) if you don't want a 0 displayed e.g. "no sale"Code:=if(isblank(g4),0,IF(ISERROR(FIND("/",G4,1)),1,2))
Excel 2003 user (and starting to warm to Excel 2007)
Thank you so much again man!!! You rockOriginally Posted by deadlyduck
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks