Sumproduct question

[cwallace70]

I would like to perform a sumproduct for the following but I keep getting value as the sum. The formula is as follows
=SUMPRODUCT(--(A2:A50000="LU"),--(LEFT(E2:E50000,1)="K"),--(H2:H50000))

My spreadsheet have a dept in column A, a code in column E and an amount in column H.
I want to get a sum if column A have the dept code of "LU" and a code of "H" then sum all the amounts in column H that fits those first two criteria's. I am using excel 2003 and cannot get this formula to work. Please help. What am I doing wrong

 

[Aladin Akyurek]

I copied and paste the formula but I don't quite understand it and it is not working. Please explain.

Thanks

What does "not working" mean - some error or incorrect outcome?

 

[Fazza]

I was thinking about a pivot table, but when I tried to execute it, I kept getting an error message

What error message? Please explain

 

[cwallace70]

I am getting a zero

 

[Aladin Akyurek]

I am getting a zero

Care to post the data you have in A2:A5, E2:E5, anf H2:H5?

 

[cwallace70]

A2:A5 is a dept code ex. 5U
E2:E5 is a policy type (H,K or G)
and H2:H5 is an amount. for example $241.56
J2:J5 renewal type of either NV or blank cell.

I need a formula for the following...If A2:A5 have a dept code of 5U
and in E2:E5 a policy code of H
and inJ2:J5 a renewal type of blank cell or NV
then get the sum from H2:H5.

Cna this be rolled up to a summary sheet for 12 monthly tabs?

I hope I explained it well. Thanks

 

[Aladin Akyurek]

A2:A5 is a dept code ex. 5U
E2:E5 is a policy type (H,K or G)
and H2:H5 is an amount. for example $241.56
J2:J5 renewal type of either NV or blank cell.

I need a formula for the following...If A2:A5 have a dept code of 5U
and in E2:E5 a policy code of H
and inJ2:J5 a renewal type of blank cell or NV
then get the sum from H2:H5.

Cna this be rolled up to a summary sheet for 12 monthly tabs?

I hope I explained it well. Thanks

Firstly, you did not provide the data I asked for. Secondly, was the J-range mentioned in the previous posts?...

Adjust to suit:

=SUMPRODUCT($H$2:$H$5,--($A$2:$A$5="5U"),--($E$2:$E$5="H"),($J$2:$J$5="")+($J$2:$J$5="NV"))

 

[cwallace70]

I apologize J was not included in the original post.

can this be rolled up to a summary without hardcoding each Dept seperatley.
<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=64 height=19> Dept</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>A</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>E</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>H</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>J</TD></TR><TR style="HEIGHT: 30pt" height=40><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30pt; BACKGROUND-COLOR: transparent" height=40 x:num>2</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>AGENCY</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>POLICY TYPE</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>FIRST YEAR</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>REN TYPE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num>3</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DE</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str="K ">K </TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="3.71">3.71 </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">NV</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num>4</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">DE</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">H</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="18.12">18.12 </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:str=" "></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20 x:num>5</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5U</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">H</TD><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="7.62">7.62 </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">NV</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21 x:num>6</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">6M</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:str="K ">K </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:num="12.12">12.12 </TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" x:str=" "> </TD></TR></TBODY></TABLE>


=SUMPRODUCT(--($A$3:$A$50001=$L7),--(LEFT($E$3:$E$50001,1)=Q$1),$I$3:$I$50001)+($J$3:$J$50001=NV)

 

[cwallace70]

=SUMPRODUCT(--($A$3:$A$50001=$L4),--(LEFT($E$3:$E$50001,1)=M$1),$H$3:$H$50001)

This formula worked for what I am trying to accoomplish but how do I add J2:J5 for NV or a blank cell to the above formula?

 

[cwallace70]

=SUMPRODUCT(--($A$3:$A$50001=$L4),--(LEFT($E$3:$E$50001,1)=M$1),$H$3:$H$50001)

The formula above worked for what I am trying to accomplish, but how to I include J2:J5 in this formula for NV or Blank?

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$3:$A$50001=$L4),--(LEFT($E$3:$E$50001,1)=M$1),$H$3:$H$50001)

The formula above worked for what I am trying to accomplish, but how to I include J2:J5 in this formula for NV or Blank?

Try...

=SUMPRODUCT(
   $H$3:$H$50001,
   --($A$3:$A$50001=$L4),
   --(LEFT($E$3:$E$50001,1)=M$1),
   ($J$3:$J$50001="")+($J$3:$J$50001="NV"))