[Solved] Count data, using multiple criteria

[pac626]

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

[FJCC]

Try

Code: Select all

SUMPRODUCT(XYZ.G5:G250 = "RL"; XYZ.K5:K250 >=1)

Note the . between the sheet name and the cell range, not a !.

[pac626]

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

[RusselB]

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

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

[Villeroy]

If you are using Excel:

Code: Select all

=sumproduct(or(xyz!g5:g250="AB";xyz!g5:g250="CD";xyz!g5:g250="EF") * (XYZ!K5:F250>=1))

[MrProgrammer]

Hi, and welcome to the forum.

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.

[Villeroy]

And since pivot tables (aka "data pilots") are so easy.
https://wiki.openoffice.org/wiki/Docume ... /DataPilot

t100833.ods

(19.2 KiB) Downloaded 103 times

[pac626]

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.