Count data in one column if certain criteria exists in another.
 

If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B). Example:
Col A Col B Col C
X 1 3
3 3
2 2
X 1 1
3 3
I would want the formula to tell me I have two 3's in Col C.

I am currently using this beautiful formula to tell me the number of 1's,
2's, and 3's in Col B:
=SUMPRODUCT(--($B$2:$B$332=""),--($I$2:$I$332=ROWS($1:1)))
Don't know if it can be massaged to work as stated for Col C?

Also: Some of my cells in Col C hold more than one number (seperated by
comma's). Any way to get this same formula to search these cells as well?

Thank you!
--
Carol

Count data in one column if certain criteria exists in another.
 

Try this in row two somewhere.

=if(and(isblank(A2),C2<B2),COUNTIF($B$2:$B$332,$B 2),"")


"Carol" wrote:

If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B). Example:
Col A Col B Col C
X 1 3
3 3
2 2
X 1 1
3 3
I would want the formula to tell me I have two 3's in Col C.

I am currently using this beautiful formula to tell me the number of 1's,
2's, and 3's in Col B:
=SUMPRODUCT(--($B$2:$B$332=""),--($I$2:$I$332=ROWS($1:1)))
Don't know if it can be massaged to work as stated for Col C?

Also: Some of my cells in Col C hold more than one number (seperated by
comma's). Any way to get this same formula to search these cells as well?

Thank you!
--
Carol

Count data in one column if certain criteria exists in another
 

Hm. I actually have many columns and rows here. I updated your formula
below to indicate appropriate columns:
=IF(AND(ISBLANK(B2),K2<I2),COUNTIF($I$2:$I$332,$I 2),"") I am not getting
any result at all. Not sure I understand the formula well enough to
trouble-shoot... Let me try to explain what I am trying to do a little more
clearly:
Col B is either blank, or contains an X (X indicates trainins is complete).
Col I indicates a group of workers. Col K indicates a second group of
workers who back-up the workers in Col I. (Col K might have more than one
group listed - and may match number in Col I.) I have 332 rows, I am trying
to get Excel to tell me how many workers in Col K have not been trained, and
are not a match to the same group in Col I.
Yikes... I hope that helps!
--
Carol


"Barb Reinhardt" wrote:

Try this in row two somewhere.

=if(and(isblank(A2),C2<B2),COUNTIF($B$2:$B$332,$B 2),"")


"Carol" wrote:

If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B). Example:
Col A Col B Col C
X 1 3
3 3
2 2
X 1 1
3 3
I would want the formula to tell me I have two 3's in Col C.

I am currently using this beautiful formula to tell me the number of 1's,
2's, and 3's in Col B:
=SUMPRODUCT(--($B$2:$B$332=""),--($I$2:$I$332=ROWS($1:1)))
Don't know if it can be massaged to work as stated for Col C?

Also: Some of my cells in Col C hold more than one number (seperated by
comma's). Any way to get this same formula to search these cells as well?

Thank you!
--
Carol

Count data in one column if certain criteria exists in another.
 

Another thought ..
Source range assumed in A1:C100
Input numbers assumed in D1 down, eg: 3, 1, 2, etc

Then in E1:
=SUMPRODUCT(($A$1:$A$100="")*($C$1:$C$100=$B$1:$B$ 100)*($B$1:$B$100=D1))
Copy E1 down. Col E returns the required counts for the inputs in col D
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B). Example:
Col A Col B Col C
X 1 3
3 3
2 2
X 1 1
3 3
I would want the formula to tell me I have two 3's in Col C.

I am currently using this beautiful formula to tell me the number of 1's,
2's, and 3's in Col B:
=SUMPRODUCT(--($B$2:$B$332=""),--($I$2:$I$332=ROWS($1:1)))
Don't know if it can be massaged to work as stated for Col C?

Also: Some of my cells in Col C hold more than one number (seperated by
comma's). Any way to get this same formula to search these cells as well?

Thank you!
--
Carol

Count data in one column if certain criteria exists in another
 

Carol,
You may like to try my thoughts expressed in the other branch ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count data in one column if certain criteria exists in another
 

Thank you Max. I don't want to seem ungrateful, but, I'm confused! Not
sure what the extra columns are for, or what I would be assuming in Col E?

If it makes this easier, I can "clean up" Col C so that there is only one
number in each cell. Would that help?
--
Carol


"Max" wrote:

Another thought ..
Source range assumed in A1:C100
Input numbers assumed in D1 down, eg: 3, 1, 2, etc

Then in E1:
=SUMPRODUCT(($A$1:$A$100="")*($C$1:$C$100=$B$1:$B$ 100)*($B$1:$B$100=D1))
Copy E1 down. Col E returns the required counts for the inputs in col D
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B). Example:
Col A Col B Col C
X 1 3
3 3
2 2
X 1 1
3 3
I would want the formula to tell me I have two 3's in Col C.

I am currently using this beautiful formula to tell me the number of 1's,
2's, and 3's in Col B:
=SUMPRODUCT(--($B$2:$B$332=""),--($I$2:$I$332=ROWS($1:1)))
Don't know if it can be massaged to work as stated for Col C?

Also: Some of my cells in Col C hold more than one number (seperated by
comma's). Any way to get this same formula to search these cells as well?

Thank you!
--
Carol

Count data in one column if certain criteria exists in another
 

Carol,

Your orig. post had a conflict <g

The earlier suggested formula was to reconcile with your statement:
.. I would want the formula to tell me I have two 3's in Col C.

If, however, your intent is really to do this:
If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B).

then we could use this amendment in E1, copied down:
=SUMPRODUCT(($A$1:$A$100="")*($C$1:$C$100<$B$1:$B $100)*($B$1:$B$100=D1))

And if you want to use col C as the "inputs" instead of a separate listing
in col D, just change the: D1 in either of the 2 formulas suggested to C1.
Then you could place the formula in D1, and copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
Thank you Max. I don't want to seem ungrateful, but, I'm confused! Not
sure what the extra columns are for, or what I would be assuming in Col E?

If it makes this easier, I can "clean up" Col C so that there is only one
number in each cell. Would that help?
--
Carol

Count data in one column if certain criteria exists in another
 

Hi again Max.

But, that is what I want to be able to do: Count the number of 3's (or 2's,
or 1's) in Col C:

Col A tells me (via X or Blank) if a worker has been trained (X=trained).
Col B tells me the group the worker is assigned to.
Col C tells me the group the worker backs-up.

I have 6 groups of workers. I have a formula that tells me the number of
people in Col B that need to be trained in each of these groups. I need a
formula that tells me the number of people in Col C that back-up the groups
in Col B - and are not trained. The catch is that sometimes Col C is
the same number as Col B. I don't want to "double-count" these folks. So,
if the number in Col C matches the number in Col B, I want it "thrown out".

Is that better? I did try your original though: Adding columns, and
etc... but it didn't work. I'm sorry if I'm not making this clear. Not
sure how to better explain it!
Thank you for your patience!

--
Carol


"Max" wrote:

Carol,

Your orig. post had a conflict <g

The earlier suggested formula was to reconcile with your statement:
.. I would want the formula to tell me I have two 3's in Col C.

If, however, your intent is really to do this:
If Column A is Blank, and the number listed in Column C is different than the
number listed in Column B, then I want to count the number of instances that
particular number occurs (in column B).

then we could use this amendment in E1, copied down:
=SUMPRODUCT(($A$1:$A$100="")*($C$1:$C$100<$B$1:$B $100)*($B$1:$B$100=D1))

And if you want to use col C as the "inputs" instead of a separate listing
in col D, just change the: D1 in either of the 2 formulas suggested to C1.
Then you could place the formula in D1, and copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
Thank you Max. I don't want to seem ungrateful, but, I'm confused! Not
sure what the extra columns are for, or what I would be assuming in Col E?

If it makes this easier, I can "clean up" Col C so that there is only one
number in each cell. Would that help?
--
Carol

Count data in one column if certain criteria exists in another
 

Try this, which is configured to suit your actual set up (gathered from your
earlier description in your response to Barb)

Place in any starting cell, say, L2:
=SUMPRODUCT(($B$2:$B$332<"X")*($I$2:$I$332=ROW(A1 ))*($K$2:$K$332=ROW(A1)))
Copy down by 6 rows to L7. L2:L7 should return the required results for
groups 1 to 6. Above of course, assumes that col K contains only single group
numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
Hi again Max.

But, that is what I want to be able to do: Count the number of 3's (or 2's,
or 1's) in Col C:

Col A tells me (via X or Blank) if a worker has been trained (X=trained).
Col B tells me the group the worker is assigned to.
Col C tells me the group the worker backs-up.

I have 6 groups of workers. I have a formula that tells me the number of
people in Col B that need to be trained in each of these groups. I need a
formula that tells me the number of people in Col C that back-up the groups
in Col B - and are not trained. The catch is that sometimes Col C is
the same number as Col B. I don't want to "double-count" these folks. So,
if the number in Col C matches the number in Col B, I want it "thrown out".

Is that better? I did try your original though: Adding columns, and
etc... but it didn't work. I'm sorry if I'm not making this clear. Not
sure how to better explain it!
Thank you for your patience!

--
Carol

Count data in one column if certain criteria exists in another
 

Sorry Max. This is not working - it returns a result on each row of "0".
That is not correct, and I was wanting a total at the bottom of the column,
not one for each row.

Not sure how to better explain my need. Thanks anyway.
--
Carol


"Max" wrote:

Try this, which is configured to suit your actual set up (gathered from your
earlier description in your response to Barb)

Place in any starting cell, say, L2:
=SUMPRODUCT(($B$2:$B$332<"X")*($I$2:$I$332=ROW(A1 ))*($K$2:$K$332=ROW(A1)))
Copy down by 6 rows to L7. L2:L7 should return the required results for
groups 1 to 6. Above of course, assumes that col K contains only single group
numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
Hi again Max.

But, that is what I want to be able to do: Count the number of 3's (or 2's,
or 1's) in Col C:

Col A tells me (via X or Blank) if a worker has been trained (X=trained).
Col B tells me the group the worker is assigned to.
Col C tells me the group the worker backs-up.

I have 6 groups of workers. I have a formula that tells me the number of
people in Col B that need to be trained in each of these groups. I need a
formula that tells me the number of people in Col C that back-up the groups
in Col B - and are not trained. The catch is that sometimes Col C is
the same number as Col B. I don't want to "double-count" these folks. So,
if the number in Col C matches the number in Col B, I want it "thrown out".

Is that better? I did try your original though: Adding columns, and
etc... but it didn't work. I'm sorry if I'm not making this clear. Not
sure how to better explain it!
Thank you for your patience!

--
Carol

Count data in one column if certain criteria exists in another
 

Based on your first post in this thread, explain how you arrive at:

I would want the formula to tell me I have two 3's in Col C.

If Column A is Blank, and the number listed in Column C is different than
the
number listed in Column B, then I want to count the number of instances
that
particular number occurs (in column B).

Based on my understanding of your explanation the result should be 0.

There are NO rows where col A is blank and col B and C are different.

Biff

"Carol" wrote in message
...
Sorry Max. This is not working - it returns a result on each row of "0".
That is not correct, and I was wanting a total at the bottom of the
column,
not one for each row.

Not sure how to better explain my need. Thanks anyway.
--
Carol


"Max" wrote:

Try this, which is configured to suit your actual set up (gathered from
your
earlier description in your response to Barb)

Place in any starting cell, say, L2:
=SUMPRODUCT(($B$2:$B$332<"X")*($I$2:$I$332=ROW(A1 ))*($K$2:$K$332=ROW(A1)))
Copy down by 6 rows to L7. L2:L7 should return the required results for
groups 1 to 6. Above of course, assumes that col K contains only single
group
numbers
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
Hi again Max.

But, that is what I want to be able to do: Count the number of 3's (or
2's,
or 1's) in Col C:

Col A tells me (via X or Blank) if a worker has been trained
(X=trained).
Col B tells me the group the worker is assigned to.
Col C tells me the group the worker backs-up.

I have 6 groups of workers. I have a formula that tells me the number
of
people in Col B that need to be trained in each of these groups. I
need a
formula that tells me the number of people in Col C that back-up the
groups
in Col B - and are not trained. The catch is that sometimes Col C
is
the same number as Col B. I don't want to "double-count" these folks.
So,
if the number in Col C matches the number in Col B, I want it "thrown
out".

Is that better? I did try your original though: Adding columns, and
etc... but it didn't work. I'm sorry if I'm not making this
clear. Not
sure how to better explain it!
Thank you for your patience!

--
Carol

Count data in one column if certain criteria exists in another
 

Hope you're still following the discussion, Carol.
We haven't given up, neither should you <g

.. This is not working - it returns a result on each row of "0".

No, it shouldn't, unless the result is really zero.

Here's a quick working sample to illustrate my last response:
http://cjoint.com/?ezip7mOpcF
Carol_wks_1.xls

Based on the re-enactment of your sample data set in the sample file ..
in L2:L7, we'd get these numbers:

0 < Group 1
1 < Group 2
2 < Group 3
0 < Group 4
0 < Group 5
0 < Group 6

You could then easily sum L2:L7 in another cell to get the total for all 6
groups.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" wrote:
Sorry Max. This is not working - it returns a result on each row of "0".
That is not correct, and I was wanting a total at the bottom of the column,
not one for each row.

Not sure how to better explain my need. Thanks anyway.
--
Carol

Count data in one column if certain criteria exists in another
 

Ah, well. Not that it matters anymore.
The cjoint link will expire in 14 days
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count data in one column if certain criteria exists in another
 

Hi Max. I did follow your link, and, while it didn't work for my purposes it
did illuminate (to me) that I was not explaining the problem appropriately.
Once I figured out what I really needed, I was able to use information from
other posts to define my formula. (You were the biggest help as I finally
did realize I needed another column.)

Sorry for all I put you through, but truly do appreciate your help!
--
Carol


"Max" wrote:

Ah, well. Not that it matters anymore.
The cjoint link will expire in 14 days
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Count data in one column if certain criteria exists in another
 

Carol,
No prob. Thanks for dropping a closure line here. All the best.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carol" toft.com wrote in message
...
Hi Max. I did follow your link, and, while it didn't work for my purposes
it
did illuminate (to me) that I was not explaining the problem
appropriately.
Once I figured out what I really needed, I was able to use information
from
other posts to define my formula. (You were the biggest help as I finally
did realize I needed another column.)

Sorry for all I put you through, but truly do appreciate your help!
--
Carol