Count once multiple CountIF criteria...

[ProfitMajin]

Hi All,

Apologies if this is a repeat of another question, couldn't find one which quite matched...

I'm afraid I've reached the point where I must ask for assistance, I'm trying to count the number of rows in a given dataset which match a set of criteria, but specifically count the row only once.

The worksheets I'm working from record our customers' usage of the various areas of the product, and I'm trying to count the number of customers with usage, so we have a layout similar to the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Name[/TD]
[TD]Stat1[/TD]
[TD]Stat2[/TD]
[TD]Stat3[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Customer1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Customer2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The trouble I'm having is that when I use =COUNTIF(C:C,">=1")+COUNTIF(D:D,">=1")+COUNTIF(E:E,">=1") it will count the cells, but if there is usage in two columns as above, it counts the row twice, and as such in the above example it returns 3, when it should return 2.

I did also try =COUNTIF(C:E,">=1") which worked the same as above, I used the longer version above in hopes it would count separately but alas

I've also tried =SUMPRODUCT((C:C>=1)+(D:D>=1)+(E:E>=1)) which also counts the cells, but of course adds the results together rather than count 1 per row.

I'm kinda hoping I've been incredibly silly with this, but after referencing several sources it seems I'm trying to use a 3D array which would naturally be more in depth than the above functions, I can provide real examples if it helps more.

Thank you all for your time!

Martin

 

[Fishboy]

Hi All,

Apologies if this is a repeat of another question, couldn't find one which quite matched...

I'm afraid I've reached the point where I must ask for assistance, I'm trying to count the number of rows in a given dataset which match a set of criteria, but specifically count the row only once.

The worksheets I'm working from record our customers' usage of the various areas of the product, and I'm trying to count the number of customers with usage, so we have a layout similar to the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Name[/TD]
[TD]Stat1[/TD]
[TD]Stat2[/TD]
[TD]Stat3[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Customer1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Customer2[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

The trouble I'm having is that when I use =COUNTIF(C:C,">=1")+COUNTIF(D:D,">=1")+COUNTIF(E:E,">=1") it will count the cells, but if there is usage in two columns as above, it counts the row twice, and as such in the above example it returns 3, when it should return 2.

I did also try =COUNTIF(C:E,">=1") which worked the same as above, I used the longer version above in hopes it would count separately but alas

I've also tried =SUMPRODUCT((C:C>=1)+(D:D>=1)+(E:E>=1)) which also counts the cells, but of course adds the results together rather than count 1 per row.

I'm kinda hoping I've been incredibly silly with this, but after referencing several sources it seems I'm trying to use a 3D array which would naturally be more in depth than the above functions, I can provide real examples if it helps more.

Thank you all for your time!

Martin

Hi Martin, welcome to the boards.

If I am understanding you correctly, the issue you are having is that Customer2 for example counts twice as there is a 1 in 2 different stat columns. If that is the case you could try this instead:

=IF(COUNTIF(C2:E2,">=1"),1,0)

So basically if the count of numbers in C:E that are equal to or greater than 1 is not zero, count it once, otherwise count it as a zero.

 

[Eric W]

Try:

=SUM(IF(C1:C10+D1:D10+E1:E10,1))
confirmed with Control-Shift-Enter.

I'd also recommend using your actual ranges instead of entire column references.

 

[ProfitMajin]

Hi Fishboy!

Thank you for replying so quickly, I tried what you suggested but it returned only 1 (I did also expand it to =IF(COUNTIF(C:E,">=1"),1,0) ), my guess is that it's looking at the entire countif as one logic test, and saying that yes it meets that critera (one of the values is 1), so it returns a 1, rather than 1 per true logic test.

I did also true using ctrl+shift+enter to turn it into an array, but not joy

Martin

 

[Fishboy]

Hi Fishboy!

Thank you for replying so quickly, I tried what you suggested but it returned only 1 (I did also expand it to =IF(COUNTIF(C:E,">=1"),1,0) ), my guess is that it's looking at the entire countif as one logic test, and saying that yes it meets that critera (one of the values is 1), so it returns a 1, rather than 1 per true logic test.

I did also true using ctrl+shift+enter to turn it into an array, but not joy

Martin

Definitely the way you have updated my formula would only produce a 1 so I think I must be misunderstanding what / how you are trying to count.

 

[Eric W]

Fishboy, your formula would work for an individual row, in your example, row 2. I believe the OP is asking for a count of customers with at least one non-zero value in columns C:E.

ProfitMajin, please try the formula in post #3.

 

[ProfitMajin]

Hi Eric,

That's worked brilliantly!! Thank you very much, been working on this for a while and it was driving me mad. At first the formula didn't work as I included the header row, after setting the range correctly it gave me exactly what I was looking for

Thanks again!

 

[Eric W]

I'm happy you got it working!

 

[Fishboy]

Fishboy, your formula would work for an individual row, in your example, row 2. I believe the OP is asking for a count of customers with at least one non-zero value in columns C:E.

ProfitMajin, please try the formula in post #3.

Indeed, I think I had misunderstood what the OP was trying to do.

 

[ProfitMajin]

Try:

=SUM(IF(C1:C10+D1:D10+E1:E10,1))
confirmed with Control-Shift-Enter.

I'd also recommend using your actual ranges instead of entire column references.

Hi Eric,

Wanted to let you know you inspired me to tweak with this formula further and have ended up with the following:
{=SUM(IFERROR(IF(C:C+D:D+E:E,1),0))}

Adding in the IFERROR part has allowed me to use the column range instead of a set range length, and if any errors are entered it won't break the formula!

Thank you both so much for your quick responses, one thing I wanted to ask just to get my head around it; the IF function requires a logic test, yet our logic test here is simply to add the three columns values on a per row basis (as per the array). How does the logic test quantify as true in order to return the true value of 1? Is it a native property of the function that it ignores values of 0 as though they were blank?

Thanks again for your help folks!