count values in one column per value in another

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!
 
J

Jacob Skaria

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
 
E

Eduardo

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)
 
M

mooresk257

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")
 
M

mooresk257

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!
 
E

Eduardo

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))
 
Top