Formula Question

[danb3x]

I have the following formula:
=SUM(SUMIFS('Raw Data'!J3:J25000,'Raw Data'!A3:A25000,{"400","401","405"},'Raw Data'!J3:J25000,">0"))

The values "400", "401" and "405" will change, can I put those values in three separate cells and then reference the cells in this formula?

Thanks,
Dan

 

[DanteAmor]

Try this

=SUMPRODUCT(('Raw Data'!J3:J25000>0)*(('Raw Data'!A3:A25000=Q1)+('Raw Data'!A3:A25000=Q2)+('Raw Data'!A3:A25000=Q3))*('Raw Data'!J3:J25000))

 

[steve the fish]

Yes you can as long as the cells are contiguous.

 

[danb3x]

Try this

=SUMPRODUCT(('Raw Data'!J3:J25000>0)*(('Raw Data'!A3:A25000=Q1)+('Raw Data'!A3:A25000=Q2)+('Raw Data'!A3:A25000=Q3))*('Raw Data'!J3:J25000))

Dante, thank you, this worked!

 

[DanteAmor]

Dante, thank you, this worked!

I'm glad to help you. Thanks for the feedback.

 

[Phuoc]

Or try:

=SUMPRODUCT(SUMIFS('Raw Data'!J3:J25000,'Raw Data'!A3:A25000,Q1:Q3,'Raw Data'!J3:J25000,">0"))