Counting occurances based on multiple variables
 

I have a range of cells that hold 4 possible entries and another range that
holds 3 possible entries. Is it possible to COUNTIF/SUMPRODUCT if you for
more than one possibile entry in a cell range?

Here's an example:

Corp Sales 15
Corp Retrn 20
Corp Excha 20
Corp Retrn 0
Corp Excha 15

I want to count the number of occurances when column B reads either Retrn or
Excha and column C reads 15 or 20. In this case, the formula would return 3.

Any help would be appreciated...

Counting occurances based on multiple variables
 

Something structured like this:

=SUMPRODUCT(--((B2:B100="Retrn")+(B2:B100="Excha")),--((C2:C100=15)+(C2:C100=20)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DSKR" wrote:

I have a range of cells that hold 4 possible entries and another range that
holds 3 possible entries. Is it possible to COUNTIF/SUMPRODUCT if you for
more than one possibile entry in a cell range?

Here's an example:

Corp Sales 15
Corp Retrn 20
Corp Excha 20
Corp Retrn 0
Corp Excha 15

I want to count the number of occurances when column B reads either Retrn or
Excha and column C reads 15 or 20. In this case, the formula would return 3.

Any help would be appreciated...

Counting occurances based on multiple variables
 

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B5,{"Retrn","Excha"},0))),--(ISNUMBER(MATCH(C1:C5,{15,20},0))))

--
Biff
Microsoft Excel MVP


"DSKR" wrote in message
...
I have a range of cells that hold 4 possible entries and another range that
holds 3 possible entries. Is it possible to COUNTIF/SUMPRODUCT if you for
more than one possibile entry in a cell range?

Here's an example:

Corp Sales 15
Corp Retrn 20
Corp Excha 20
Corp Retrn 0
Corp Excha 15

I want to count the number of occurances when column B reads either Retrn
or
Excha and column C reads 15 or 20. In this case, the formula would return
3.

Any help would be appreciated...

Counting occurances based on multiple variables
 

=SUMPRODUCT(--((B2:B100="Retrn")+(B2:B100="Excha")),--((C2:C100=15)+(C2:C100=20)))

No need for the double unary:

=SUMPRODUCT((B2:B100="Retrn")+(B2:B100="Excha"),(C 2:C100=15)+(C2:C100=20))

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
Something structured like this:

=SUMPRODUCT(--((B2:B100="Retrn")+(B2:B100="Excha")),--((C2:C100=15)+(C2:C100=20)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DSKR" wrote:

I have a range of cells that hold 4 possible entries and another range
that
holds 3 possible entries. Is it possible to COUNTIF/SUMPRODUCT if you
for
more than one possibile entry in a cell range?

Here's an example:

Corp Sales 15
Corp Retrn 20
Corp Excha 20
Corp Retrn 0
Corp Excha 15

I want to count the number of occurances when column B reads either Retrn
or
Excha and column C reads 15 or 20. In this case, the formula would
return 3.

Any help would be appreciated...

Counting occurances based on multiple variables
 

Thanks!!!!!

"Luke M" wrote:

Something structured like this:

=SUMPRODUCT(--((B2:B100="Retrn")+(B2:B100="Excha")),--((C2:C100=15)+(C2:C100=20)))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DSKR" wrote:

I have a range of cells that hold 4 possible entries and another range that
holds 3 possible entries. Is it possible to COUNTIF/SUMPRODUCT if you for
more than one possibile entry in a cell range?

Here's an example:

Corp Sales 15
Corp Retrn 20
Corp Excha 20
Corp Retrn 0
Corp Excha 15

I want to count the number of occurances when column B reads either Retrn or
Excha and column C reads 15 or 20. In this case, the formula would return 3.

Any help would be appreciated...