Sumproduct formula is double counting a range

[rrmando18]

Hello party people. I don't have much experience with the SUMPRODUCT function and it's kicking my butt. I have to criteria in the formula below and the result is double counting the (Paychex!$AA$5:$AA$100) range. Why is that? If my range is two columns there's no problem but when I use only one column in the range it double counts. How do I fix it or is there a more efficient formula I can use? Thanks.


I want to add the values in ((Paychex!$W$5:$X$100)+(Paychex!$AA$5:$AA$100)+(Paychex!$AB$5:$AC$100)+(Paychex!$AF$5:$AG$100) based on the two criteria (Paychex!$A$5:$A$100=Journal!$B$3)*(Paychex!$J$5:$J$100=Journal!$E$1)


=SUMPRODUCT((Paychex!$A$5:$A$100=Journal!$B$3)*(Paychex!$J$5:$J$100=Journal!$E$1)*((Paychex!$W$5:$X$100)+(Paychex!$AA$5:$AA$100)+(Paychex!$AB$5:$AC$100)+(Paychex!$AF$5:$AG$100)))

 

[Peter_SSs]

Welcome to posting on the MrExcel board!

is there a more efficient formula I can use?

Try this

=SUM(FILTER(CHOOSECOLS(Paychex!$W$5:$AG$100,1,2,5,6,7,10,11),(Paychex!$A$5:$A$100=Journal!$B$3)*(Paychex!$J$5:$J$100=Journal!$E$1),0))

BTW, what is the name of the sheet that this formula is on?

 

[rrmando18]

Hello. The name of the sheet the formula is in is Journal. Thanks!

 

[rrmando18]

Welcome to posting on the MrExcel board!


Try this

=SUM(FILTER(CHOOSECOLS(Paychex!$W$5:$AG$100,1,2,5,6,7,10,11),(Paychex!$A$5:$A$100=Journal!$B$3)*(Paychex!$J$5:$J$100=Journal!$E$1),0))

BTW, what is the name of the sheet that this formula is on?

Thank you so much Peter....that formula worked like a charm!

 

[Peter_SSs]

Thank you so much Peter....that formula worked like a charm!

You're welcome. Thanks for the confirmation.

The name of the sheet the formula is in is Journal.

In that case I would suggest changing the formula as it is a bad idea to use the sheet name that the formula is on when referencing cells/ranges on that sheet. It can lead to incorrect results in some circumstances.
Use this instead

=SUM(FILTER(CHOOSECOLS(Paychex!$W$5:$AG$100,1,2,5,6,7,10,11),(Paychex!$A$5:$A$100=$B$3)*(Paychex!$J$5:$J$100=$E$1),0))