Excel/IF + COUNTIF?

Advertisement


Question
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.
Thanks

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.
However,
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.
Thanks

Answer
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

=sumproduct(--(data!a2:a2000=1),--(data!c2:c2000=1))

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 support@flameenterprises.co.uk - I think the use of SumProduct may have to form a blog entry on my site www.flameenterprises.co.uk too!
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Aidan Heritage

Expertise

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

Experience

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.

Education/Credentials
I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!

©2016 About.com. All rights reserved.