Tweaking of an sum"If" fomula to an "IF AND" and also refer specific cell values

[Pumpk1n]

I would like to refer to a value in column A for each row. For example, when the value in A2 = the value in column J and the values are greater than V2 (8/29/17) and less than X2 (9/13/17), then sum column P. The formula works, but it's a lot of copying and updating formulas as I need to copy it for 50 rows (50 different member IDs) and then copy it for 8 different date fields. Any help would be greatly appreciated!

=SUMIFS(Gold!$P:$P,Gold!$J:$J,"136065",Gold!$L:$L,">08/29/2017",Gold!$L:$L,"<09/13/2017")

 

[FDibbins]

Maybe change the hard-coded criteria to cell references, then just change those as needed?

 

[Pumpk1n]

Thanks -- the issue is that I don't know how to reference a cell when the value is in quotes. I changed it to "a8", but that isn't quite right as it returned 0.
=SUMIFS(Gold!$P:$P,Gold!$J:$J,"'a8'",Gold!$L:$L,">08/15/2017",Gold!$L:$L,"<08/30/2017")

 

[FDibbins]

Almost there
=SUMIFS(Gold!$P:$P,Gold!$J:$J,a8,Gold!$L:$L,">08/15/2017",Gold!$L:$L,"<08/30/2017")

 

[Pumpk1n]

Almost there
=SUMIFS(Gold!$P:$P,Gold!$J:$J,a8,Gold!$L:$L,">08/15/2017",Gold!$L:$L,"<08/30/2017")

It's awesome! Thank you so much!!

 

[FDibbins]

Happy to help