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

[Guest]

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!

 

[Guest]

Try this in row two somewhere.

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

 

[Guest]

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,$I2),"") 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!

 

[Guest]

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

 

[Guest]

Carol,
You may like to try my thoughts expressed in the other branch ..

---

 

[Guest]

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?

 

[Guest]

Carol,

Your orig. post had a conflict <g>

The earlier suggested formula was to reconcile with your statement:
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.

---

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

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.

 

[T. Valko]

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

 

[Guest]

Hope you're still following the discussion, Carol.

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

Ah, well. Not that it matters anymore.
The cjoint link will expire in 14 days

---

 

[Guest]

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!

 

[Max]

Carol,
No prob. Thanks for dropping a closure line here. All the best.