[Solved] Count data, using multiple criteria

Discuss the spreadsheet application

[Solved] Count data, using multiple criteria

Postby pac626 » Tue Jan 21, 2020 11:49 pm

Hello, I am new to this forum so I hope I am posting in the correct area.
I am trying to come up with a calc formula where I am on one page but need to collect data from another page. I have 3 salespeople that I am tracking certain information on. First I will post an example of what I am trying to do but the formula is off.
=COUNTIF('XYZ'!G5:G250,"RL")+COUNTIF('XYZ'!K5:K250,">=1")
What I am trying to do is this. If on page xyz...G5:G250 contains the name RL and K5:K250 is greater than one....count the sum of K5:K250 only if the G column contains the

It almost works except it just counts G5:G250 containg RL and sums it up with K5:K250. I've also tried sumproduct formulas as well havina real struggle here

Any help would be appreciated. Thank you so much
Last edited by Hagar Delest on Sat Jan 25, 2020 1:39 pm, edited 2 times in total.
Reason: tagged solved
OpenOffice 4.1.6 on Windows 10
pac626
 
Posts: 3
Joined: Tue Jan 21, 2020 6:12 pm

Re: Need help with a Calc formula

Postby FJCC » Tue Jan 21, 2020 11:57 pm

Try
Code: Select all   Expand viewCollapse view
SUMPRODUCT(XYZ.G5:G250 = "RL"; XYZ.K5:K250 >=1)

Note the . between the sheet name and the cell range, not a !.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7744
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Count data, using multiple criteria

Postby pac626 » Thu Jan 23, 2020 8:57 pm

Thank you do much for your help but I think I didn’t explain properly. I’m going to take out of the equation for the minute that I am pulling from another page.

On row G for example I will have 3 people’s initials...AB, CD, and EF. On row K I will have a dollar amount. I am trying not to add the dollar amounts but count each time anything over 1 in row K where the AB person is located on the adjacent G column
OpenOffice 4.1.6 on Windows 10
pac626
 
Posts: 3
Joined: Tue Jan 21, 2020 6:12 pm

Re: Count data, using multiple criteria

Postby RusselB » Thu Jan 23, 2020 9:49 pm

In Calc, rows are numbered, not lettered. Columns are lettered, thus you can not be using row G. I'm guessing, like I suspect FJCC did, that you meant columns G & K, not rows.

I suspect, based on your second post, that you misunderstand how the SUMPRODUCT function works when using comparisons as parameters.

You should be able to replace the first parameter in FJCC's code with an OR statement, so that you would end up with something like
Code: Select all   Expand viewCollapse view
=sumproduct(or(xyz.g5:g250="AB";xyz.g5:g250="CD";xyz.g5:g250="EF");XYZ.K5:F250>=1)


If you find that you are having trouble incorporating the formulas we are giving you with your actual data, then upload a sample of your spreadsheet with some dummy data so that we can return your sample spreadsheet with the formulas properly incorporated.
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.
User avatar
RusselB
Moderator
 
Posts: 6103
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Count data, using multiple criteria

Postby Villeroy » Thu Jan 23, 2020 9:58 pm

If you are using Excel:
Code: Select all   Expand viewCollapse view
=sumproduct(or(xyz!g5:g250="AB";xyz!g5:g250="CD";xyz!g5:g250="EF") * (XYZ!K5:F250>=1))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Count data, using multiple criteria

Postby MrProgrammer » Fri Jan 24, 2020 12:33 am

Hi, and welcome to the forum.

pac626 wrote: I will have 3 people’s initials...AB, CD, and EF. On row K I will have a dollar amount. I am trying not to add the dollar amounts but count each time anything over 1 in row K
A simple way to get a count for the three people:
=SUMPRODUCT(XYZ.G5:G250="AB";XYZ.K5:K250>=1)
+SUMPRODUCT(XYZ.G5:G250="CD";XYZ.K5:K250>=1)
+SUMPRODUCT(XYZ.G5:G250="EF";XYZ.K5:K250>=1)

Another way, shorter but more abstruse:
=SUMPRODUCT(XYZ.G5:G250={"AB";"CD";"EF"};XYZ.K5:K250>={1;1;1})

[Tutorial] The SUMPRODUCT function, example X24
You should use the formula that you understand.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3960
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Count data, using multiple criteria

Postby Villeroy » Fri Jan 24, 2020 1:34 pm

And since pivot tables (aka "data pilots") are so easy.
https://wiki.openoffice.org/wiki/Docume ... /DataPilot
t100833.ods
(19.2 KiB) Downloaded 15 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Count data, using multiple criteria

Postby pac626 » Sat Jan 25, 2020 4:17 am

Thank you to everyone I was able to solve it with your help. I’m actually using this on Google Docs and grabbing data from one page to another.

=SUMPRODUCT(‘HONDA R’!G5:G250=“RL,’HONDA R!K5:250>=1)

That is my exact formula for my situation and did exactly what I needed. Thank you so much. I’m also going to play with done if the other shorter ways I see here too for fun.
OpenOffice 4.1.6 on Windows 10
pac626
 
Posts: 3
Joined: Tue Jan 21, 2020 6:12 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 8 guests