Sumproduct with mulitple criteria containing equals and or's
 

I need to sum a column only when multiple criteria is met. Example:

Column A is Priority = 1
Column B is type of application = could contain one of the following or all
four (Application and/or Lotus and/or Mainframe and/or Image Dev)
Column C is the number of calls per application = is an amount for each
entry in Column B (Application 20, Lotus 10, Mainframe 10 and Image Dev 10.

In this example all of the types applications will be reported the name,
Applications, so the result I need is:

Priority 1
Applications 50

I have tried multiple sumproduct versions and either get a result of 20 or
zero

How do I mix equals and or conditions in a sumproduct or should I use a
different method.

Any help would be appreciated.

--
Mike

Sumproduct with mulitple criteria containing equals and or's
 

If you only have one condition you can use SUMIF (quicker and easier
than SUMPRODUCT):

=SUMIF(A:A,1,C:C)

If your priorities are text values, then you will need to make it:

=SUMIF(A:A,"1",C:C)

Hope this helps.

Pete

On Sep 23, 4:40*pm, Mike wrote:
I need to sum a column only when multiple criteria is met. Example:

Column A is Priority = 1
Column B is type of application = could contain one of the following or all
four (Application and/or Lotus and/or Mainframe and/or Image Dev)
Column C is the number of calls per application = is an amount for each
entry in Column B (Application 20, Lotus 10, Mainframe 10 and Image Dev 10.

Sumproduct with mulitple criteria containing equals and or's
 

Thanks but I have multiple conditions to meet.

If Column A is Priority 1 and column B equals Application or Lotus or
Mainframe or Image Dev then sum Column C.

Application, Lotus, Mainframe and Image Dev I have in separate columns such as

Application
Lotus
Mainframe
Image Dev.

I have tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*($B$13:$B$74=G114)*($B$13:$B$74=G115)*($B$13:$B$ 74=G116)*$C$13:$C$74)

Where a13:a74 is column A and C111 equals 1, b13:b74 is Column B and G113
thru G116 equals Application, Lotus, Mainframe and Image Dev respectively and
c13:c74 is Column C that contains the numbers I need to sum.

also tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*(or($B$13:$B$74=G114)*(or($B$13:$B$74=G115)*(or( $B$13:$B$74=G116)*$C$13:$C$74)

--
Mike


"Pete_UK" wrote:

If you only have one condition you can use SUMIF (quicker and easier
than SUMPRODUCT):

=SUMIF(A:A,1,C:C)

If your priorities are text values, then you will need to make it:

=SUMIF(A:A,"1",C:C)

Hope this helps.

Pete

On Sep 23, 4:40 pm, Mike wrote:
I need to sum a column only when multiple criteria is met. Example:

Column A is Priority = 1
Column B is type of application = could contain one of the following or all
four (Application and/or Lotus and/or Mainframe and/or Image Dev)
Column C is the number of calls per application = is an amount for each
entry in Column B (Application 20, Lotus 10, Mainframe 10 and Image Dev 10.

In this example all of the types applications will be reported the name,
Applications, so the result I need is:

Priority 1
Applications 50

I have tried multiple sumproduct versions and either get a result of 20 or
zero

How do I mix equals and or conditions in a sumproduct or should I use a
different method.

Any help would be appreciated.

--
Mike

Sumproduct with mulitple criteria containing equals and or's
 

Try it like this:

=SUMPRODUCT(--(A13:A74=C111),--(ISNUMBER(MATCH(B13:B74,G113:G116,0))),C13:C74)

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
Thanks but I have multiple conditions to meet.

If Column A is Priority 1 and column B equals Application or Lotus or
Mainframe or Image Dev then sum Column C.

Application, Lotus, Mainframe and Image Dev I have in separate columns
such as

Application
Lotus
Mainframe
Image Dev.

I have tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*($B$13:$B$74=G114)*($B$13:$B$74=G115)*($B$13:$B$ 74=G116)*$C$13:$C$74)

Where a13:a74 is column A and C111 equals 1, b13:b74 is Column B and G113
thru G116 equals Application, Lotus, Mainframe and Image Dev respectively
and
c13:c74 is Column C that contains the numbers I need to sum.

also tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*(or($B$13:$B$74=G114)*(or($B$13:$B$74=G115)*(or( $B$13:$B$74=G116)*$C$13:$C$74)

--
Mike


"Pete_UK" wrote:

If you only have one condition you can use SUMIF (quicker and easier
than SUMPRODUCT):

=SUMIF(A:A,1,C:C)

If your priorities are text values, then you will need to make it:

=SUMIF(A:A,"1",C:C)

Hope this helps.

Pete

On Sep 23, 4:40 pm, Mike wrote:
I need to sum a column only when multiple criteria is met. Example:

Column A is Priority = 1
Column B is type of application = could contain one of the following or
all
four (Application and/or Lotus and/or Mainframe and/or Image Dev)
Column C is the number of calls per application = is an amount for each
entry in Column B (Application 20, Lotus 10, Mainframe 10 and Image Dev
10.

In this example all of the types applications will be reported the
name,
Applications, so the result I need is:

Priority 1
Applications 50

I have tried multiple sumproduct versions and either get a result of 20
or
zero

How do I mix equals and or conditions in a sumproduct or should I use a
different method.

Any help would be appreciated.

--
Mike

Sumproduct with mulitple criteria containing equals and or's
 

HHi

=SUMPRODUCT(($A$13:$A$74=$C$111),($B$13:$B$74=G113 )+($B$13:$B$74=G114)+($B$13:$B$74=G115)+($B$13:$B$ 74=G116),$C$13:$C$74)



"Mike" wrote:

Thanks but I have multiple conditions to meet.

If Column A is Priority 1 and column B equals Application or Lotus or
Mainframe or Image Dev then sum Column C.

Application, Lotus, Mainframe and Image Dev I have in separate columns such as

Application
Lotus
Mainframe
Image Dev.

I have tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*($B$13:$B$74=G114)*($B$13:$B$74=G115)*($B$13:$B$ 74=G116)*$C$13:$C$74)

Where a13:a74 is column A and C111 equals 1, b13:b74 is Column B and G113
thru G116 equals Application, Lotus, Mainframe and Image Dev respectively and
c13:c74 is Column C that contains the numbers I need to sum.

also tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*(or($B$13:$B$74=G114)*(or($B$13:$B$74=G115)*(or( $B$13:$B$74=G116)*$C$13:$C$74)

--
Mike


"Pete_UK" wrote:

If you only have one condition you can use SUMIF (quicker and easier
than SUMPRODUCT):

=SUMIF(A:A,1,C:C)

If your priorities are text values, then you will need to make it:

=SUMIF(A:A,"1",C:C)

Hope this helps.

Pete

On Sep 23, 4:40 pm, Mike wrote:
I need to sum a column only when multiple criteria is met. Example:

Column A is Priority = 1
Column B is type of application = could contain one of the following or all
four (Application and/or Lotus and/or Mainframe and/or Image Dev)
Column C is the number of calls per application = is an amount for each
entry in Column B (Application 20, Lotus 10, Mainframe 10 and Image Dev 10.

In this example all of the types applications will be reported the name,
Applications, so the result I need is:

Priority 1
Applications 50

I have tried multiple sumproduct versions and either get a result of 20 or
zero

How do I mix equals and or conditions in a sumproduct or should I use a
different method.

Any help would be appreciated.

--
Mike

Sumproduct with mulitple criteria containing equals and or's
 

Beautiful,,,,,,,,,,,,,,,you guys and gals always come
through,,,,,,,,,,,,,,,,,,,thanks so much this saves me so much time in
converting data I am receiving from a new system.

Thanks
--
Mike


"T. Valko" wrote:

Try it like this:

=SUMPRODUCT(--(A13:A74=C111),--(ISNUMBER(MATCH(B13:B74,G113:G116,0))),C13:C74)

--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
Thanks but I have multiple conditions to meet.

If Column A is Priority 1 and column B equals Application or Lotus or
Mainframe or Image Dev then sum Column C.

Application, Lotus, Mainframe and Image Dev I have in separate columns
such as

Application
Lotus
Mainframe
Image Dev.

I have tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*($B$13:$B$74=G114)*($B$13:$B$74=G115)*($B$13:$B$ 74=G116)*$C$13:$C$74)

Where a13:a74 is column A and C111 equals 1, b13:b74 is Column B and G113
thru G116 equals Application, Lotus, Mainframe and Image Dev respectively
and
c13:c74 is Column C that contains the numbers I need to sum.

also tried
=SUMPRODUCT(($A$13:$A$74=$C$111)*($B$13:$B$74=G113 )*(or($B$13:$B$74=G114)*(or($B$13:$B$74=G115)*(or( $B$13:$B$74=G116)*$C$13:$C$74)

--
Mike


"Pete_UK" wrote:

If you only have one condition you can use SUMIF (quicker and easier
than SUMPRODUCT):

=SUMIF(A:A,1,C:C)

If your priorities are text values, then you will need to make it:

=SUMIF(A:A,"1",C:C)

Hope this helps.

Pete

On Sep 23, 4:40 pm, Mike wrote:
I need to sum a column only when multiple criteria is met. Example:

Column A is Priority = 1
Column B is type of application = could contain one of the following or
all
four (Application and/or Lotus and/or Mainframe and/or Image Dev)
Column C is the number of calls per application = is an amount for each
entry in Column B (Application 20, Lotus 10, Mainframe 10 and Image Dev
10.

In this example all of the types applications will be reported the
name,
Applications, so the result I need is:

Priority 1
Applications 50

I have tried multiple sumproduct versions and either get a result of 20
or
zero

How do I mix equals and or conditions in a sumproduct or should I use a
different method.

Any help would be appreciated.

--
Mike