SOLVED

Multiply cells based on another sheet

Copper Contributor

Hi everyone,

I am trying to make Sheet 1 total each row based on the services provided. I will input the quantity of each service and I need it to multiply with the values on Sheet 2. I have multiple project types with variable pricing rates.

So basically I need some sort of "multiply if" equation. Any advice?

TIA!

 

Sheet 1:

sdude1_0-1717896064142.png

 

Sheet 2:

sdude1_2-1717895764966.png

 

3 Replies
best response confirmed by sdude1 (Copper Contributor)
Solution

Hi @sdude1 

 

With Table Pricing:

Sample.png

 

Sample2.png

Total:

=IF(ISNUMBER([@Type]),
  SUM(
    FILTER(Pricing[[Service 1]:[Service 4]], Pricing[Type] = [@Type]) *
    Table1[@[Service 1]:[Service 4]]
  ),
  ""
)

 

Thank you! This worked and taught me so much!

@sdude1 

As variant

=SUM(
  Table1[@[Service 1]:[Service 4]]*
  XLOOKUP([@Type],Pricing[Type],Pricing[[Service 1]:[Service 4]],0)
 )
1 best response

Accepted Solutions
best response confirmed by sdude1 (Copper Contributor)
Solution

Hi @sdude1 

 

With Table Pricing:

Sample.png

 

Sample2.png

Total:

=IF(ISNUMBER([@Type]),
  SUM(
    FILTER(Pricing[[Service 1]:[Service 4]], Pricing[Type] = [@Type]) *
    Table1[@[Service 1]:[Service 4]]
  ),
  ""
)

 

View solution in original post