How can we use Multiplication in Sumproduct

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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???

Code:
 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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
We cant see your worksheet but that said that formula can only ever produce 0. Its not possible that cells in a range are equal to "Sum of ABC PM" and also equal to "EA".
 
Upvote 0
Hi,
Here i trying to find the columns by column header and from those columns i need to apply the below formula in last empty column
This formula was done manually but i am trying this in VBA

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


Below is the code which i tried but i couldn't perform multiplication.
Code:
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


Regards,
Dhruv
 
Upvote 0
So in your formula you have C2,D2,F2,G2 and H2 yet in your code just Sum of ABC PM and EA. How can we know which ones are fixed and which ones are the headers?
 
Upvote 0
My first row contains headers and from that headers i need to find the value using the formula , but each and every time my columns might be varying so i need to find the columns through the column header and apply that formula

Code:
=SUMPRODUCT(((" & Hdr & "=""Sum of ABC PM"")/31)*(" & Hdr & "=""EA""))+(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPL""))-(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPLR"")),(rc1:rc[-1]))
 
Last edited:
Upvote 0
=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?
 
Upvote 0
=ROUND(SUMPRODUCT(D2/31*G2)+SUMPRODUCT(C2/H2*E2)-SUMPRODUCT(C2/H2*F2),0) ,This formula can be applied in excel sheet directly but i am trying that in vba so
Code:
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"")/31)*(" & Hdr & "=""EA""))+(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPL""))-(((" & Hdr & "=""Prev"")/(" & Hdr & "=""PEA""))*(" & Hdr & "=""PPLR"")),(rc1:rc[-1]))"
End With
Instead of using the formula in excel sheet i am trying to perform that using vba by the above code but it's throwing me '#DIV/0!' error

My main goal is to search for particular columns using column headers and perform the above formula in VBA
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top