Sumproduct with mulitple criteria containing equals and or's

[Mike]

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.

 

[Pete_UK]

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

 

[Mike]

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)

 

[T. Valko]

Try it like this:

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

 

[Eduardo]

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]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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