## [Solved] Count data, using multiple criteria

### [Solved] Count data, using multiple criteria

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

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
FJCC
Moderator

Posts: 7744
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Count data, using multiple criteria

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

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.

RusselB
Moderator

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

### Re: Count data, using multiple criteria

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

Villeroy
Volunteer

Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Count data, using multiple criteria

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).

MrProgrammer
Moderator

Posts: 3960
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

### Re: Count data, using multiple criteria

And since pivot tables (aka "data pilots") are so easy.
https://wiki.openoffice.org/wiki/Docume ... /DataPilot
t100833.ods
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

Villeroy
Volunteer

Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Count data, using multiple criteria

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