Excel Counts/Sums/IFs

Fed up talking videogames? Why?
User avatar
Lotus
Member
Joined in 2008

PostExcel Counts/Sums/IFs
by Lotus » Thu Dec 04, 2014 4:06 pm

So I currently have this formula in a cell: =SUMIF(F4:F66,"Won",H4:H66)

In Column F I have the status of an opportunity. In Column H I have the value of said opportunity.

The above formula tells me the Sum of those opportunities with a status of “Won”.
However, rather than a Sum of the values, I want a Count of the number with status “Won”.

How do I do that? COUNTIF AND COUNTIFS don’t seem to want to work. Any ideas?

User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel Counts/Sums/IFs
by rinks » Thu Dec 04, 2014 6:35 pm

I'm not really sure from your description what column H is actually for, but if you just want a count of the number of "wins", you would only need to refer to column F anyway (if column F is where the "won" status is recorded).

So COUNTIF really should work:
COUNTIF(F4:F66,"Won")

Loves us all since 2008
User avatar
Tomous
Member
Joined in 2010
AKA: Vampbuster

PostRe: Excel Counts/Sums/IFs
by Tomous » Fri Dec 05, 2014 11:21 am

COUNTIF works for me too. If you can't get it working for whatever reason, what you could do is add a column I and put this formula in:

=IF(F4="Won",1,0)

Then amend your original formula to

=SUMIF(F4:F66,"Won",I4:I66)


This will then add up all the 1s and therefore give you the total number of "wins". You can obviously hide Column I too to make it look neater.

Image
User avatar
Lotus
Member
Joined in 2008

PostRe: Excel Counts/Sums/IFs
by Lotus » Fri Dec 05, 2014 11:26 am

Thanks for the reply rinks. I perhaps need to explain myself better. I get what you're saying above though, and it makes sense.

Essentially I've got a spreadsheet of opportunities; opportunities on the rows, details about them in the columns.

Column F shows the status of an opportunity (pending, won, etc) and columns H onwards show months, with values in each row for how much each opportunity is worth (and therefore how much will be brought in that month).

What you've said above would work for historic months, as the only items left in those columns will be "Won" (anything not won gets moved into the next month). So with that I could quickly see how many wins there were in October, say.

The problem is that for the current month, there's a mixture of won/pending/whatever, and I only want to count the wins in that month. I can do a Sum of the wins, and assumed I'd be able to do a count just as easily, but it doesn't seem to work like that, unless I'm missing something obvious.

User avatar
rinks
Member
Member
Joined in 2008
Location: Aboard the train that goes around the world

PostRe: Excel Counts/Sums/IFs
by rinks » Fri Dec 05, 2014 7:43 pm

Just checking I've got this right: you want to count how many rows have a status of "Won" in column F, and have a positive numeric value in column H? If that's the case, this should work:

=SUMPRODUCT((F4:F66="Won")*(H4:H66>0))

Loves us all since 2008
User avatar
Lotus
Member
Joined in 2008

PostRe: Excel Counts/Sums/IFs
by Lotus » Mon Dec 08, 2014 10:51 am

That's bang on rinks, thank you. Very much appreciated.


Return to “Stuff”

Who is online

Users browsing this forum: Albert, Godzilla, Grumpy David, Ploiper, Rubix, shy guy 64, wensleydale and 454 guests