How can we use Multiplication in Sumproduct

[GirishDhruva]

Hi Everyone,
Here i am trying to multiply a particular columns using SUMPRODUCT but its giving me the output as '0'

Can i know why is this happening???

 With Worksheets("Pivot")    finalcolumn = 1 + .Cells(2, 1).End(xlToRight).Column
    .Cells(2, finalcolumn).Select
    Nxt = .Cells(2, .Columns.Count).End(xlToLeft).Offset(, 1).Column
    Hdr = .Range(.Cells(1, 1), .Cells(1, Nxt - 1)).Address(, , xlR1C1)
    Range(.Cells(2, Nxt), .Cells(.Rows.Count, Nxt - 1).End(xlUp).Offset(, 1)).FormulaR1C1 = _
        "=SUMPRODUCT(((" & Hdr & "=""Sum of ABC PM"")/30)*(" & Hdr & "=""EA""),(rc1:rc[-1]))"
End With

Any Suggestions/Solutions for the above code

Regards,
Dhruv

 

[GirishDhruva]

=ROUND(SUMPRODUCT(D2/31*G2)+SUMPRODUCT(C2/H2*E2)-SUMPRODUCT(C2/H2*F2),0)

What words are in C1, D1, E1, F1, G1 and H1? I presume those are the header cells yes? Why are we using sumproduct here?

Yes, 1st row contains headers.

If i am not wrong by using this SUMPRODUCT we could reduce the looping(instead of looping in every cell and calculating i tried using SUMPRODUCT)

 

[steve the fish]

I really cant help until you answer the question.

 

[GirishDhruva]

What exactly is your question?????

 

[steve the fish]

=ROUND(SUMPRODUCT(D2/31*G2)+SUMPRODUCT(C2/H2*E2)-SUMPRODUCT(C2/H2*F2),0)

What words are in C1, D1, E1, F1, G1 and H1? I presume those are the header cells yes? Why are we using sumproduct here?

You havent answered the first question.

 

[GirishDhruva]

Oh sorry, those are C1(row-1,column-C),D1(row-1,column-D),E1(row-1,column-E)..........
I think u haven't seen my workbook, once check my workbook which i have shared.

Regards,
Dhruv

 

[steve the fish]

What are these headers in words? Not their positions. Their values.

 

[GirishDhruva]

It will be differed every week so only i am trying to perform calculations based on Column Header

 

[steve the fish]

So you want to perform calcualtions based on a column header but have no idea what that column header will be? I doubt you can do that.

 

[GirishDhruva]

My column headers would be fixed as i have provided that column headers in my workbook

 

[steve the fish]

Then why are you talking about using column headers if they dont vary?