[Solved] Count X or Y in col B with any value in col C

[Solved] Count X or Y in col B with any value in col C

I don't know if I have the right formula, but I'm trying to find the number of times either X or Y (column B) have anything (any value) in column C.

I've tried various permutations of SUMPRODUCT but it's not working for me. I also thought about VLOOKUP but it didn't seem to fit what I needed.

Any help totally appreciated.
Attachments
Last edited by Ssronica on Mon Apr 06, 2020 12:38 am, edited 2 times in total.
OpenOffice 3.4.1;Win7
Ssronica

Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUMPRODUCT

You don't show the SUMPRODUCT formula that you tried, but reading through the SUMPRODUCT Tutorial shows me what I think the problem you encountered was.
You want to sum if the entry in column B is X or Y, but the SUMPRODUCT, for comparisons, uses an AND, not an OR, thus I'd suggest using two SUMPRODUCTs then adding them together, giving
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(B3:B12="Y";C3:C12)+SUMPRODUCT(B3:B12="X";C3:C12)
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SUMPRODUCT

RusselB, thank you for responding.

I tried the formula you suggested, but it added the amounts next to either X or Y, rather than telling me how many times X or Y had something in column C. Sorry if I miscommunicated!
OpenOffice 3.4.1;Win7
Ssronica

Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUMPRODUCT

Also, I just wanted to address that I was using the formula SUMPRODUCT(A1:A6="red"; B1:B6="big"; C1:C6) that I got from the Openoffice wiki, but I removed one array because it was not needed. It multiplied the values rather than counting them, which is why I'm not sure SUMPRODUCT is the correct formula to use.
OpenOffice 3.4.1;Win7
Ssronica

Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUMPRODUCT

I suggest you read the tutorial I referenced earlier. The Wki's aren't always the best resource.
That said,
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(B3:B12="Y";C3:C12>0)+SUMPRODUCT(B3:B12="X";C3:C12>0)
seems to work for what you are looking for.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6062
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: SUMPRODUCT

Another alternative might be a pivot table - see the attached example.

ssronica.ods
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine

robleyd
Moderator

Posts: 3396
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SUMPRODUCT

Ssronica wrote:RusselB, thank you for responding.

I tried the formula you suggested, but it added the amounts next to either X or Y, rather than telling me how many times X or Y had something in column C. Sorry if I miscommunicated!

If by "something" you mean an actual amount (i.e. not zero), RusselB's answer above should do the job.
If, on the other hand, you want any entered value (including zero and negative) counted, ISNUMBER() is your friend. See if you can use one of these:
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(ISTEXT(B3:B12);ISNUMBER(C3:C12))
=SUMPRODUCT(B3:B12="X";ISNUMBER(C3:C12))+SUMPRODUCT(B3:B12="Y";ISNUMBER(C3:C12))
=SUMPRODUCT(NOT(ISERROR(FIND(B3:B12;"XY")));ISNUMBER(C3:C12))

The first one does not check for a valid label letter, just that there is some text there. The second one is just an elaboration on RusselB's formula, counting separately for each specified label. The third one is less rigid (accepts labels "X", "Y" and "XY") but easier to extend if you need to have value labels other than X and Y.

Pivot tables, as suggested by robleyd, are very useful and flexible, but there are a few pitfalls. Most importantly in your case perhaps: they are not "live" like formulas are. A couple of mouse clicks are required to refresh the table after entering data. (For large data tables this is a good thing, because then you don't need to wait for recalculation after each time you fill in a cell.)

keme
Volunteer

Posts: 3358
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: [SOLVED] Count X •or• Y in col B with any value in col C

Keme, thank you. That second one did the job. I used the first part (before the +) because I wasn't looking to total X and Y, I'm using the formula separately for X & Y.

RusselB and Robleyd, thank you also for your help. I knew you guys would be awesome. Cheers.

This has been marked as solved.
OpenOffice 3.4.1;Win7
Ssronica

Posts: 32
Joined: Wed Sep 25, 2013 11:41 pm