Count if the sum of columns meet a condition

Hi Sunshine (in the morning makes me happy)

See the attached for your formula.
of =COUNTIF(B2:B11,">0")

If you want to count the customers for each year, try this in cell B12 and copy/paste to the other cells in row 12

Please Login or Register  to view this content.

Dang it, Marvin the Martian beat me to it

Maybe:

Formula:

Please Login or Register  to view this content.

and drag across.


Regards, TMS

I am probably being a little slow here, but coud you provide a few sample answers (and why)?

edit: told you I was being slow

Hey Trevor,

Can CountIf() ever return a negative number? I'm pondering the "<>0" in your answer above...

I guess, in this specific example, it's not likely to But, maybe they refunded some money or paid compensation. But, I was just putting in the belt and braces.

As a generality: sure.

And no reason why "not equal to zero" is any better or worse than "greater than zero" in general terms. At least, I don't think so. Or maybe it forces Excel to do two comparisons, in which case maybe it's over-engineered. But, for 11 columns, I don't think it's a big deal. Open debate ensues ...

For example, column E count should be 7, because even Customer F didn't buy anything in column E (2007), but it's a customer. It came back in 2008 and bought something.

In that case, the answer is 10 in every column because they were all customers in 2004, even if they didn't buy anything in later years

Is Customer A a customer in 2014 or not. He was every year up until 2013 but not in 2014, and we don't have any indication that he will be in 2015. What if a customer doesn't buy anything for two or three years? When is a customer not a customer.

Sorry, no idea how to make that logic work ... I'm out.

Good luck with someone else providing a solution that caters for the gaps in customer loyalty/continuity

I am strugling with that too

Im still waiting to see your WB updated with some sample answers (along with why)

Ok, it sucks that a few are having a problem with this, but, it's nice to know that I am not the only one!

Originally Posted by my_sunshine

Some customers don't buy anything for a year and then come back, which should be included in the count for that year. For example, customer C, F and H should be included in the count for the years 2012, 2007, and 2012 & 2013 respectively.

So the logic is to count all customers who had sales in that particular year.....but also any customers who have made purchases in both past and future years.

Try this formula in B12 copied across

=SUMPRODUCT(((B2:B11>0)+(COUNTIF(OFFSET($A2:A2,ROW(A2:A11)-ROW(A2),0),">0")*COUNTIF(OFFSET(C2:$Z2,ROW(B2:B11)-ROW(B2),0),">0")>0)>0)+0)

That will cater for future expansion up to column Z

@DLL: that's brilliant ... but I would have thought 2012 would equal 4 not 5 although I think you're right as customers seem to be counted way into the future.

Very clever. No idea how you come up with something like this.

Regards, TMS

If every customer bought something in 2015, that would set the count to 10 for every year right back to the beginning. Is that the desired outcome?

I guess we need to wait to see a reply and (preferably) a WB with some sample answers in it

OK then,

Here is another answer that is a CSE (Control+Shift+Enter) array answer.

In B12 put

= SUMPRODUCT(((B2:B11=0))*($L2:$L11>0))+COUNTIF(B2:B11,">0")

and then confirm it with a CSE.

You're welcome. Thanks for the rep.

Originally Posted by MarvinP

= SUMPRODUCT(((B2:B11=0))*($L2:$L11>0))+COUNTIF(B2:B11,">0")

Hello Marvin,

You don't require CSE for this formula

Note that although the results for this formula and my suggestion are the same for the sample given they might not always be if the data changes. For example your formula will count a customer who only bought something in 2014 as a customer in 2007. Mine will only count customers with both previous and future year purchases (as well as the ones in the current year)