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

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

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

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

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

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

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

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

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

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