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

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

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

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

"ONJNo1" wrote in message
...
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...

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

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" wrote in message
...
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...

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

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

"PCLIVE" wrote:

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

"ONJNo1" wrote in message
...
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...

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

Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down your
criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down your
criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

"Dave Thomas" wrote...
....
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))

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

No they have not been moved

Peo


"Dave Thomas" wrote in message
et...
The posts are in their proper place now. They were not in the proper place
when I looked at them. They have been moved.

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down your
criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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" wrote in message
...
No they have not been moved

Peo


"Dave Thomas" wrote in message
et...
The posts are in their proper place now. They were not in the proper
place when I looked at them. They have been moved.

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down your
criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

Moved where?

Peo


"Dave Thomas" wrote in message
...
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" wrote in message
...
No they have not been moved

Peo


"Dave Thomas" wrote in message
et...
The posts are in their proper place now. They were not in the proper
place when I looked at them. They have been moved.

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down
your criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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" wrote in message
...
Moved where?

Peo


"Dave Thomas" wrote in message
...
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" wrote in message
...
No they have not been moved

Peo


"Dave Thomas" wrote in message
et...
The posts are in their proper place now. They were not in the proper
place when I looked at them. They have been moved.

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down
your criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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

Peo


"Dave Thomas" wrote in message
...
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" wrote in message
...
Moved where?

Peo


"Dave Thomas" wrote in message
...
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" wrote in message
...
No they have not been moved

Peo


"Dave Thomas" wrote in message
et...
The posts are in their proper place now. They were not in the proper
place when I looked at them. They have been moved.

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down
your criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...

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

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!


"Peo Sjoblom" wrote in message
...
You can't see that you posted the same message 3 times?

Peo


"Dave Thomas" wrote in message
...
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" wrote in message
...
Moved where?

Peo


"Dave Thomas" wrote in message
...
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" wrote in message
...
No they have not been moved

Peo


"Dave Thomas" wrote in message
et...
The posts are in their proper place now. They were not in the proper
place when I looked at them. They have been moved.

"Peo Sjoblom" wrote in message
...
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" wrote in message
et...
My post did not go to the proper place so I tried again. Shut down
your criticism of people.

"Peo Sjoblom" wrote in message
...
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"),D1:D15)


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



--
Regards,

Peo Sjoblom



"Dave Thomas" wrote in message
t...
Why the double minus when a single minus will do?

"PCLIVE" wrote in message
...
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

"ONJNo1" wrote in message
...
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...