How to apply sumproduct formula

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
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

thanks in adavance




5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
shanker
you need not repeat the formula . as I told you you just copy the formulat DOWN. SEE THE MESSAGE AGAIN. The offsets function within the formula will change it c14,c15 c16 etc. In your first message you wanted "how to apply the formula"
try on this line and if still want a macro post back.