• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiplying with criteria and them sum it up

Chief Ace

Member
Hi Excel Gurus,

Column A through AC are being pasted from another excel worksheet that was downloaded from one of the system. I know some of them are numbers but they look like a text since they lined up on the right side vice right.

Now my question is if there is another way to create a formula without using Helper Column.
As you can see in the spreadsheet, highlighted in orange that was the formula that they entered back then in cell AF39:AF45. Since it's not dynamic, cause the cell AG33 will be change depends on column U (i.e: 020, 584, D39, H13...etc) on what we are looking for. So, I created a helper column (highlighted in green) to multiply then sum it up each column (AG3:AG31, AH3:AH31...etc) and enter the amount in AF39:AF45. Thank you in advance for the help or assist very much appreciated.

Regards
Chief Ace
 

Attachments

  • RON NSF TEMPLATE1.xlsx
    21 KB · Views: 7
=SUMPRODUCT((U3:U33=$AG$33)*AC3:AC33*1.25)+SUMPRODUCT((U3:U33=AG33)*AE3:AE33*SUM(AC39:AC48))
or simplified to:
=SUMPRODUCT((U3:U33=$AG$33)*(AC3:AC33*1.25+AE3:AE33*SUM(AC39:AC48)))

now without Column AE
=SUMPRODUCT((U3:U33=$AG$33)*(AC3:AC33*1.25+(X3:X33/$X$34)*SUM(AC39:AC48)))
 
Hi Hui,

Thank you for the quick response. But it's not matching the outcome of the total sum. The output in column AF39 through AF45 should be (see below)
AF39
133.7783​
AF40
7.73325​
AF41
10.40625​
AF42
1.5525​
AF43
122.9558​
AF44
30.93075​
AF45
44.4915​

Now if you change the entry in CELL AG33 from "224" to "H46" the outcome should look like the below.
AF39
111.4819​
AF40
6.444375​
AF41
8.671875​
AF42
1.29375​
AF43
102.4631​
AF44
25.77563​
AF45
37.07625​

Again, thank you for assisting, greatly appreciate it.

regards,
Chief Ace
 
Hi Hui,

I figure it out.....I have been trying to use the SUMPRODUCT all day today at work and can't figure it out. When you responded to my inquiry that gives me another idea on how to use it. Below is the formula i used.

=SUMPRODUCT((U3:U31=$AG$33)*AE3:AE31*AC39)*SUM(AE3:AE31)

Again, Thank you for the assist and as always EXCEL NINJAS always there to lend a helping hand. :):):)

Regards,

Chief Ace.
 
Wow, that was pretty convoluted! I think your final calculation is correct. Given that I never use direct cell referencing, I finished with
Code:
= Table2[TOT TRANS AMT]
  * SUMIFS(Table1[HOURS PAID],Table1[SABRS RON],selected)
  / SUM(Table1[HOURS PAID])
or 'dressed up' a bit using 365
Code:
= LET(
  total, Table2[TOT TRANS AMT],
  percentage, SUMIFS(Table1[HOURS PAID],Table1[SABRS RON],selected) / SUM(Table1[HOURS PAID]),
  total * percentage)
 
Back
Top