Excel/IF + COUNTIF?
QUESTION: Hi Aidan,
In sheet DATA I have a list of deals sold to clients.
Each deal is a row.
In column A there is the week number in the year.
In column B there is the revenues for the deal.
In column C states whether the client is new (1) or old (2).
In sheet COMPARISON I have a row for each week -
in column A I wish to count all the new deals for each week
in column B I wish to count all the old deals for each week
in column C I wish to sum all the deals for each week.
Pivot table is not aloud.
ANSWER: OK, it SOUNDS like it's a multiple condition countif or sumif - there are functions in the later versions of excel which do this (countifs and sumifs) though I actually find these a little cumbersome to use. That is probably also because I've used a trick with sumproduct for many years to do the same thing, and this page http://www.bettersolutions.com/excel/EUV214/LO231331611.htm
gives a reasonably clear explanation of how to do that
HOPEFULLY this has given you what you need but let me know if I can help further
---------- FOLLOW-UP ----------
QUESTION: Thanks for your prompt reply and lightning web site, Aidan.
My case is different than described in your web site, as I need to countif the appearances of week 1, but only the new ones (1 in column C), and then, only the old ones (2 in column C).
Same goes for SUMIF.
In other words, there is a second condition to the counting within column C.
I hope this is clear.
yes, it is clear, and the sumproduct function does exactly that - the link mentioned using the multiplier sign, I tend to use double negatives, but the principle still works
would give you a count of all cases where there was a 1 in column a of Data AND a 1 in column C - amending this as needed would allow you to get the counts you want.
If it is still not clear, I'm more than happy to look at a sample file - my direct email is email@example.com - I think the use of SumProduct may have to form a blog entry on my site www.flameenterprises.co.uk too!