[Solved] Conditional Counting

Discuss the spreadsheet application

[Solved] Conditional Counting

Postby ammodramus » Wed Sep 13, 2017 4:58 pm

Hello,

I am trying to figure out how to count the numbers in a row based on the contents of another row. The contents of the second row consists of zeroes and ones; I want to count those cells in the first row which correspond to zeroes in the second. One laborious and impractical way to do this would be AND(B30>0 ;B41=0)+AND(C30>0;C41=0)+AND(D30>0;D41=0)+...+AND(DR30>0;DR41=0).

Is there a simpler way to accomplish this? I could of course create a new row with the AND statements and then sum it, but I would strongly prefer not to because I want to do this for many rows of data.

Thanks for any help!

Shane
Last edited by ammodramus on Wed Sep 13, 2017 6:17 pm, edited 1 time in total.
OpenOffice 4 on Windows 7
ammodramus
 
Posts: 2
Joined: Wed Sep 13, 2017 4:50 pm

Re: Conditional Counting

Postby acknak » Wed Sep 13, 2017 5:39 pm

Greetings and welcome to the community forum!

If A1:A9 are the values and B1:B9 are the zeroes/ones, either of these will work:

=COUNTIFS(A1:A9;"<>""""";B1:B9;0)
=SUMPRODUCT(A1:A9<>"";B1:B9=0)

These count any non-empty cells in the first range, where the corresponding cell in the second range is zero.
Attachments
countifs_sample.ods
(9.23 KiB) Downloaded 2 times
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22547
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Conditional Counting

Postby ammodramus » Wed Sep 13, 2017 6:16 pm

Thank you!

Does the "<>" condition select for all cells within the range that are not blank?

In my case, I have zeroes in those cells I don't want to count, so COUNTIFS(B16:DR16;">0";B41:DR41;0) worked well.

Thanks again for the help!

Shane
OpenOffice 4 on Windows 7
ammodramus
 
Posts: 2
Joined: Wed Sep 13, 2017 4:50 pm

Re: Conditional Counting

Postby acknak » Wed Sep 13, 2017 6:53 pm

ammodramus wrote:Does the "<>" condition select for all cells within the range that are not blank?

Yes

In my case, I have zeroes in those cells I don't want to count, so COUNTIFS(B16:DR16;">0";B41:DR41;0) worked well.

Seems reasonable. That will also skip negative values.

You also have to be careful as to the source of the values: if they come from a calculation, it may produce values very close to (but not quite) zero, and the condition <>0 will be true when it shouldn't be.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22547
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3


Return to Calc

Who is online

Users browsing this forum: Robindude and 30 guests