Multiplying with criteria and them sum it up

[Chief Ace]

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

 

[Hui]

=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)))

 

[Chief Ace]

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

 

[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.

 

[Peter Bartholomew]

Wow, that was pretty convoluted! I think your final calculation is correct. Given that I never use direct cell referencing, I finished with

= Table2[TOT TRANS AMT]
  * SUMIFS(Table1[HOURS PAID],Table1[SABRS RON],selected)
  / SUM(Table1[HOURS PAID])

or 'dressed up' a bit using 365

= LET(
  total, Table2[TOT TRANS AMT],
  percentage, SUMIFS(Table1[HOURS PAID],Table1[SABRS RON],selected) / SUM(Table1[HOURS PAID]),
  total * percentage)