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

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

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

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

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

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

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

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

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