SUMPRODUCT - How to combine
 

A1=SUMPRODUCT(($F$4:$F$1008={"P1","R1","U1","X1"}) *(($E$4:$E$1008)=$E1015)*($Z$4:$Z$1008))

B1=SUMPRODUCT(($F$4:$F$1008={"P1R","R1R","U1R","X1 R","A1R"})*(($E$4:$E$1008)=$E1015)*($Z$4:$Z$100 8))

C1= A1-B1.
The above formulas work.
1. Can someone combine the two into 1 formula (for
neatness in the worksheet).
2. should ctrl+shift+enter be applied

Thank you in advance.
RobertR. (there is another Robert aswell in the forum

In your particular stated case, this should work. Modify to suit.
=SUMPRODUCT((LEFT(G2:G22,2)={"p1","a1"})*H2:H22)

--
Don Guillett
SalesAid Software

"Robert" wrote in message
...

A1=SUMPRODUCT(($F$4:$F$1008={"P1","R1","U1","X1"}) *(($E$4:$E$1008)=$E1015)*(
$Z$4:$Z$1008))


B1=SUMPRODUCT(($F$4:$F$1008={"P1R","R1R","U1R","X1 R","A1R"})*(($E$4:$E$1008)
=$E1015)*($Z$4:$Z$1008))

C1= A1-B1.
The above formulas work.
1. Can someone combine the two into 1 formula (for
neatness in the worksheet).
2. should ctrl+shift+enter be applied

Thank you in advance.
RobertR. (there is another Robert aswell in the forum

Robert wrote:
A1=SUMPRODUCT(($F$4:$F$1008={"P1","R1","U1","X1"}) *(($E$4:$E$1008)=$E1015)*($Z$4:$Z$1008))

B1=SUMPRODUCT(($F$4:$F$1008={"P1R","R1R","U1R","X1 R","A1R"})*(($E$4:$E$1008)=$E1015)*($Z$4:$Z$100 8))

C1= A1-B1.
The above formulas work.
1. Can someone combine the two into 1 formula (for
neatness in the worksheet).
2. should ctrl+shift+enter be applied

Thank you in advance.
RobertR. (there is another Robert aswell in the forum



1.

=SUMPRODUCT(ISNUMBER(MATCH($F$4:$F$1008,{"P1","R1" ,"U1","X1"},0))-ISNUMBER(MATCH($F$4:$F$1008,{"P1R","R1R","U1R","X1 R","A1R"},0)),--($E$4:$E$1008=$E1015),$Z$4:$Z$1008)

2.

The answer is no.

Aladin Akyurek, thank you. The "A1" was omitted in the first array. Once
corrected gave the correct number.

=SUMPRODUCT(ISNUMBER(MATCH($F$4:$F$1008,{"P1","R1" ,"U1","X1","A1"},0))-ISNUMBER(MATCH($F$4:$F$1008,{"P1R","R1R","U1R","X1 R","A1R"},0)),--($E$4:$E$1008=$E1015),$Z$4:$Z$1008)

Don Guillett, thank you also for the short formula. I had to modify to suit
my sheet. It seems to work but there is a hitch:arrays "P1","R1"... and
"P1R","R1R"..are being summed instead of being subtracted. In case you want
to check further, your formula as modified

=SUMPRODUCT((LEFT(F4:F1008,2)={"P1","A1","R1","U1" ,"X1","E1","I1","J1"})*((E4:E1008)=E1015)*(Z4:Z100 8))
Once again thank you both.
RobertR

Oops. I mis-read that you wanted to subtract one from the other. Just put
1st formula in a minus signand the second formula as you show a1-b1.

--
Don Guillett
SalesAid Software

"Robert" wrote in message
...
Aladin Akyurek, thank you. The "A1" was omitted in the first array. Once
corrected gave the correct number.


=SUMPRODUCT(ISNUMBER(MATCH($F$4:$F$1008,{"P1","R1" ,"U1","X1","A1"},0))-ISNUM
BER(MATCH($F$4:$F$1008,{"P1R","R1R","U1R","X1R","A 1R"},0)),--($E$4:$E$1008=$
E1015),$Z$4:$Z$1008)

Don Guillett, thank you also for the short formula. I had to modify to
suit
my sheet. It seems to work but there is a hitch:arrays "P1","R1"... and
"P1R","R1R"..are being summed instead of being subtracted. In case you
want
to check further, your formula as modified


=SUMPRODUCT((LEFT(F4:F1008,2)={"P1","A1","R1","U1" ,"X1","E1","I1","J1"})*((E
4:E1008)=E1015)*(Z4:Z1008))
Once again thank you both.
RobertR