How to count(if) column B IF column A says "x"

[ONJNo1]

Column A has 4 variables (w,x,y,z)
Column B has 4 variables (v1,v2,v3,v4)

I am trying to count how many times Column B says "v2" when Column A says
"x". We were previously counting Column B using COUNTIF, but we were not
accounting for Column A. Now, we need to break it down by Column A.

I saw a previous discussion about SUBTOTAL and autofiltering, but my bosses
are too lazy to filter column A to what they need when they look.. they would
like it displayed at the bottom all the time...so that wouldn't work here.

I tried IF, COUNTIF, COUNT and AND. IF came the closest, but I could not
figure out how to get the logical test to accept a range.

Thanks alot... have been trying for 4 hours now...

 

[PCLIVE]

You'll need to specify the actual range (in my example, A1:A15 and B1:B15).

=SUMPRODUCT(--(A1:A15="x"),--(B1:B15="v2"))

HTH,
Paul

 

[Dave Thomas]

You can use an array formula. Assuming your data is in A1:A10 and B1:B10
then:

Enter =SUM((A1:A10="x")*(B1:B10="v2")) in your answer cell and press
CTRL+SHIFT+ENTER. In the formula bar the formula will have curly braces {}
around it, signifying that the formula is an array formula. In the formula,
the * signifies "and". Formulas of this type can also use +, signifying "or"
to create more complex formulas.

You could also use this formula
=SUMPRODUCT(-(A1:A10="x"),-(B1:B10="v2")) However, this type of formula
allows for only "and" relationships.

 

[ONJNo1]

Wow, didn't think that would work, but it did. Thanks alot.

 

[Dave Thomas]

Why the double minus when a single minus will do?

 

[Dave Thomas]

Why the double minus when a single minus will do?

 

[Peo Sjoblom]

Because it makes sense, you don't always use even number of ranges/arrays do
you?

=SUMPRODUCT(-(A1:A15="x"),-(B1:B15="v2"),-(C1:C15="y"))

will obviously return a negative result

meaning that if we want to sum D

=SUMPRODUCT(-(A1:A15="x"),-(B1:B15="v2"),-(C1:C15="y"),D115)


the result will also be negative (or positive if the values summed in D are
negative)
so it will be an incorrect result


Note that I will only post one answer if you post the same post more than
once

 

[Dave Thomas]

My post did not go to the proper place so I tried again. Shut down your
criticism of people.

 

[Peo Sjoblom]

Really! I just noticed that you posted 3 answers to the post with the
subject line

counting x instances of a string across columns..



--
Regards,

Peo Sjoblom

 

[Dave Thomas]

The posts are in their proper place now. They were not in the proper place
when I looked at them. They have been moved.

 

[Harlan Grove]

...
....

You could also use this formula
=SUMPRODUCT(-(A1:A10="x"),-(B1:B10="v2"))
However, this type of formula allows for only "and" relationships.

....

Maybe you don't know how to use it any other way. For OR,

=SUMPRODUCT(--((A1:A10="x")+(B1:B10="v2")>0))

 

[Peo Sjoblom]

No they have not been moved

Peo

The posts are in their proper place now. They were not in the proper place
when I looked at them. They have been moved.

 

[Dave Thomas]

They are in a different place now than when I originally posted them and
looked at them. Don't tell me they didn't move.

 

[Peo Sjoblom]

Moved where?

Peo

They are in a different place now than when I originally posted them and
looked at them. Don't tell me they didn't move.

 

[Dave Thomas]

They were at the bottom of the group for the responses to the OP instead of
being under and indented to the posting I was responding to.
Then later, they had moved up into the group and were in their proper
places.

 

[Peo Sjoblom]

You can't see that you posted the same message 3 times?

Peo

 

[Dave Thomas]

I was trying to post it in the right place. Each time I posted the posting
showed up at the end. Finally the postings wound up in the right place after
a period of time. Each time I looked, the postings were in the wrong place,

NOW GET OVER IT. YOU'LL LIVE!