# How to apply sumproduct formula

Closed
shankar - Sep 6, 2010 at 01:55 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 9, 2010 at 01:30 AM
Hello,

i am facing a problem while applying the sumproduct formula. pls, solve this for me

eg
sheet2.:=SUMPRODUCT((sheet1!\$C\$2:\$C\$65536=C13)+0,(sheet1!\$G\$2:\$G\$65536="IN")+0,sheet1!\$P\$2:\$P\$65536)

could any sovle the above problem how to apply that formula

## 5 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 6, 2010 at 06:07 AM
try this on an empty cell in sheet2

=SUMPRODUCT((Sheet1!\$C\$2:\$C\$65536=C13)*(Sheet1!\$G\$2:\$G\$65536="IN")*Sheet1!\$P\$2:\$P\$65536)

here remember that c13 is in sheet2
k thank u , but i need to know apply this formula by changing the c13 to c14 and c15 and so on . so, that my resulted values will appear in the cell by cell
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 8, 2010 at 06:42 AM
use this formula in any empty cell and copy DOWN(REPEAT DOWN-not across)

=SUMPRODUCT((Sheet1!\$C\$2:\$C\$65536=OFFSET(\$C\$13,ROW(A1)-1,0,1,1))*(Sheet1!\$G\$2:\$G\$65536="IN")*Sheet1!\$P\$2:\$P\$65536)
thank u for ur valuable suggestioin but,
i need a macro to be return so, that i can avoid writing formula repeatedly

Didn't find the answer you are looking for?