Log In
Register
Home
September 26, 2018
Offline
Hi there, I'm stuck with the following problem:
I need to identify new business from a table of sales data ranging from 2014 - 2018, i.e. any sales in 2018, where either the supplier or the product did NOT have any sales in the previous years.
Please see attached example file.
In column "F" (New Biz) the value should be "New" if there were sales by a given Supplier (column A) in year 2018, but NOT in years 2017, 2016, 2015 or 2014.
And likewise for column "G" (New Product), only there it should refer to new Products (column "C").
Any help is greatly appreciated!!
Regards, Mirjam
See Answer
VIP
Trusted Members
June 25, 2016
Offline
Hi Mirjam
Give this a try.
Sunny
September 26, 2018
Offline
Hi Sunny,
Brilliant! Thank you so much for sharing your knowledge!!
I'm not sure though, if I could reproduce the formula (sure I couldn't :-)). Could you explain what the red part in the formula does and why there's an equal sign?
=IF(COUNTIFS(A:A;A3;B:B;2018)=COUNTIFS(A:A;A3);"New";"")
Thanks again,
Mirjam
VIP
Trusted Members
June 25, 2016
Offline
Hi Mirjam
COUNTIFS(A:A;A3;B:B;2018) Count the number of each Supplier in 2018
COUNTIFS(A:A;A3) Count the number of each Supplier irrespective of the year
If the Supplier only exist for 2018, both the formulas above should show the same value.
I then compare both using COUNTIFS(A:A;A3;B:B;2018)=COUNTIFS(A:A;A3).
If they are the same value, then the formula will display TRUE otherwise it will display FALSE.
With this, the formula will evaluate to IF(TRUE/FALSE;"New";"") . It will show New if TRUE else it will show a blank.
You can refer here for more details on the IF() function
https://www.myonlinetraininghu.....-explained
Hope I explained it clear enough.
Sunny
Answers Post
1 Guest(s)
