Need Help with a Formula - One more Argument

[Paul]

OK, this works perfectly:

=SUMPRODUCT((D52000="Round")*(E5:E2000>=0.01)*(E5:E2000<0.04)*(F5:F2000={"D","E","F"}))

But when I try to add one more argument, I get unexpected
results:

=SUMPRODUCT((D52000="Round")*(E5:E2000>=0.01)*(E5:E2000<0.04)*(F5:F2000={"D","E","F"})*(G5:G2000={"IF","VVS1","VVS2"}))

I am basically trying to limit the results in the first
formula with this:

G5:G2000="IF" or "VVS1" or "VVS2"

Thanks, once again, for any help.

- Paul

 

[Bob Phillips]

Paul,

I think this does what you want

=SUMPRODUCT(--($D$5:$D$10="Round"),--($E$5:$E$10>=0.01),--($E$5:$E$10<0.04),
--(ISNUMBER(MATCH($F$5:$F$10,{"D","E","F"},0))),--(ISNUMBER(MATCH($G$5:$G$10
,{"IF","WS1","WS2"},0))))

--

HTH

RP

OK, this works perfectly:

=SUMPRODUCT((D52000="Round")*(E5:E2000>=0.01)*(E5:E2000<0.04)*(F5:F2000={"
D","E","F"}))

But when I try to add one more argument, I get unexpected
results:

=SUMPRODUCT((D52000="Round")*(E5:E2000>=0.01)*(E5:E2000<0.04)*(F5:F2000={"
D","E","F"})*(G5:G2000={"IF","VVS1","VVS2"}))

I am basically trying to limit the results in the first
formula with this:

G5:G2000="IF" or "VVS1" or "VVS2"

Thanks, once again, for any help.

- Paul

 

[Guest]

Thank you very much!!

- Paul

 

[Bob Phillips]

Should have mentioned previously that the formula that you gave only worked
for rows where the F column is D and the G column is IF, or E and WS1, or F
and WS2. Whilst it could handle all 3 values, it was pairing them off.

--

HTH

RP

Thank you very much!!

- Paul