Formula help!

[Melody]

I need help for a formula (what I'm working on is at bottom) after the =SUMPRODUCT(--(B1:B100=A6) to do the following
(AS1:AS100=X2) and (AW1:AW100=X4) goes on to finish formula, but if X2 or X4 are blank it will (AS1:AS100=X2) or (AW1:AW100=X4) and then finish formula

The X2 & X4 are cells with data validation lists that contain several different codes used in the AS and AW columns
Below is what I have thus far. Hope someone understands what I need. Thanks for you help

=SUMPRODUCT(--(B1:B100=A6)*((AS1:AS100=X2)+(AW1:AW100=X4)),--( DX1X100>=DATE(H2,D1,1)),--(DX1X100<=DATE(I2,O2,30)),CJ1:CJ100

 

[Bob Phillips]

Do you mean

=IF(OR(X2="",X4=""),SUMPRODUCT(--(B1:B100=A6)*((AS1:AS100=X2)+(AW1:AW100=X4)
),--(
DX1X100>=DATE(H2,D1,1)),--(DX1X100<=DATE(I2,O2,30)),CJ1:CJ100),SUMPRODUC
T(--(B1:B100=A6)*((AS1:AS100=X2)*(AW1:AW100=X4)),--(
DX1X100>=DATE(H2,D1,1)),--(DX1X100<=DATE(I2,O2,30)),CJ1:CJ100))



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I need help for a formula (what I'm working on is at bottom) after the

=SUMPRODUCT(--(B1:B100=A6) to do the following:

(AS1:AS100=X2) and (AW1:AW100=X4) goes on to finish formula, but if X2 or

X4 are blank it will (AS1:AS100=X2) or (AW1:AW100=X4) and then finish
formula.

The X2 & X4 are cells with data validation lists that contain several

different codes used in the AS and AW columns.

 

[Frank Kabel]

hi
try changing the part
((AS1:AS100=X2)+(AW1:AW100=X4))

to
(((AS1:AS100=X2)+(AW1:AW100=X4))>=((X2<>"")+(X4<>"")))