Formula which counts numbers except if the line has a (specific word)

**DirtySkillz** · 03-25-2015, 04:49 AM

Hi All,

How are you doing,

I'm making a basic spreadsheet for work. Need some assistance on counting revenue. However want the total revenue to not count it if (the word -"lost" ) is embedded in the cell.

Can you advise what formula I should use. I tried Sumif, countif and can't seem to get a proper line to work it out.

So as you can see for Cell I9, the code should reflect the BDMS's count of revenue, but not count if cell G17 states lost.

Can anyone assist?

Thank you

**samba_ravi** · 03-25-2015, 05:04 AM

it is better if you attach a sample excel file instead of image

**DirtySkillz** · 03-25-2015, 05:13 AM

See attached.

Thank you

**Spicey_888** · 03-25-2015, 05:19 AM

Hard to understand what you are asking.

Have you tried something like this...

Formula:

Please Login or Register  to view this content.

**Spicey_888** · 03-25-2015, 05:24 AM

Formula:

Please Login or Register  to view this content.

Insert this into I9 and drag down

**DirtySkillz** · 03-25-2015, 05:30 AM

I forgot to mention (won) in G17 needs to count as a positive, would I continue to use sumif for this?

**DirtySkillz** · 03-25-2015, 06:20 AM

I tried to utilise the code:

=SUMIFS($M$17:$M$450,$C$17:$C$450,C9,$G$17:$G$450,"Open",$G$17:$G$450,"Won")

But that failed. How do I add (won) so it calculates that cell as well if selected?

**samba_ravi** · 03-25-2015, 06:39 AM

=SUMIFS($M$17:$M$450,$C$17:$C$450,$C9,$G$17:$G$450, "Won")
change / edit the "Won" as per your needs

**DirtySkillz** · 03-25-2015, 06:43 AM

Hi Nflsales, I tried to add open to the equation to count that aswell and it failed

=SUMIFS($M$17:$M$450,$C$17:$C$450,$C9,$G$17:$G$450,"Won",$G$17:$G$450,"Open")

Attached was the code used. I just need it to pick up (open and won)

Any suggestions.

**samba_ravi** · 03-25-2015, 08:55 AM

Then try
=SUM(SUMIFS($M$17:$M$450,$C$17:$C$450,$C9,$G$17:$G$450, {"Won","Open"}))

**DirtySkillz** · 03-25-2015, 09:53 PM

OK yes that worked.

Thank you for your help!

Formula which counts numbers except if the line has a (specific word)

LinkBack

Thread Tools

Rate This Thread

Display

Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Re: Formula which counts numbers except if the line has a (specific word)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula that counts specific values, but stops at the point that the value changes.

[SOLVED] Looking for a formula that counts individual numbers within 1 cell

[SOLVED] MS Word - Macro that counts the number of "~" between two specific values

[SOLVED] How do I delete specific word from every line?

[SOLVED] Remove a specific word line from a word document.

Bookmarks

Bookmarks

Posting Permissions