count values in one column per value in another

  • Thread starter Thread starter mooresk257
  • Start date Start date
M

mooresk257

This seems like it should be simple enough, but for some reason it's eluding
me today.

I want to "countif" values in one column based on a specific value in another.

For example, I have a production line A, B, or C that makes a product that
is in either a bag or a box.

So, I want to count the number of products on line A that are in a bag.

Thanks!
 
With Prouduction line in ColA and Bag/Box in Col B

Try the below formula

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Bag"))

If this post helps click Yes
 
Hi,
I assume you have your numbers in column A and if it is a bag or box in
column B, in c1 you enter the product to be counted and in D1 you want the
result so in D1 enter

=SUMPRODUCT((B1:B5=C1)*A1:A5)
 
Well that's because I'm not sure what formula to use. Let me try and explain
it this way:

I have two columns:

Line Packaging
A BAG
A BOX
A BAG
B BOX
B BOX
B BAG
B BOX
C BAG
C BOX
C BAG

I need help with a formula that will count the number of "BAG" for line "A"
- which is 2.

I tried using COUNTIF as an array formula but that doesn't work - it counts
all "BAG" for the array.

{=IF(A2:A11="A",COUNTIF(B2:B11,"BAG"),0)} which gives an answer of 5.

So what I need is a COUNTIF(array1 = "A" and array2 = "BAG")
 
This worked - but what does the "--" i nthe formula mean to Excel? I've never
seen this before, and obviously the formula doesn't work without the two
dashes.

Thanks!
 
Hi,
in C1 you enter Bag and in C2 A, you can change it to count other lines or
boxes so in D2 enter

=SUMPRODUCT((A2:A5000=C2)*(B2:B5000=C1))
 
Back
Top