# Sumproduct to Nth Number



## Bartneedshelp (Dec 19, 2022)

I am having trouble figuring out the correct formula or if it is possible in a one line solution. Here is an example

A - (1.3 x 1) , (1.1 x 5) , (1.1 x 28) = 37.6 x a 
B - (1.5 x 2) , (1 x 2) , (2 x 8) =  21 x b 
C - (1 x 10) , (2 x 5) , (3 x 3) = 29 x c


solution = (37.6 x a) + (21 x b) + (29 x c)

What formula would you use to do a repeating sumproduct to an nth number to sum the products of all the data


----------



## Z51 (Dec 19, 2022)

What does your current data look like in your worksheet?  (How is the data entered into cells, e.g. what's in A1, B1, C1, D1, A2, B2, C2, etc.?)  And why do you think SUMPRODUCT is needed?


----------



## Bartneedshelp (Dec 19, 2022)

3 sheets currently with a alot of data being sorted most of it figured out but on the last step... to your point it may not need to be a sumproduct 

highlighted is what formula i am trying to figure out - right now the highlighted cells work as A would where in this instance i am taking D:D where equal to B2 >> & C:C where equal to Sheet 1 B2 "13 - Toilet,Family" >> so where both of those are true and taking column J:J on sheet 3 gives a number >> then multiplies it to Sheet 1 D2 and additionally it would need to mulitply by Sheet 1 G1 in this case its just 1 and not in that formula yet but would need to change based on the corresponding Room name / number 

this is what i have figured out so far but in order to finish this i would need to do this repeatedly for each room #13 - 24 as shown here at least and then sum all of those


Sheet 1 






Sheet 2 & *currently cell D2*









Sheet 3


----------



## Bartneedshelp (Dec 19, 2022)

Bartneedshelp said:


> 3 sheets currently with a alot of data being sorted most of it figured out but on the last step... to your point it may not need to be a sumproduct
> 
> highlighted is what formula i am trying to figure out - right now the highlighted cells work as A would where in this instance i am taking D:D where equal to B2 >> & C:C where equal to Sheet 1 B2 "13 - Toilet,Family" >> so where both of those are true and taking column J:J on sheet 3 gives a number >> then multiplies it to Sheet 1 D2 and additionally it would need to mulitply by Sheet 1 G1 in this case its just 1 and not in that formula yet but would need to change based on the corresponding Room name / number
> 
> ...



In an attempt to explain further..... 

I am trying to figure out the total for each Work Category example is (01.02 - GENERAL REQUIREMENTS) in order to do that 
Each room has a specific cost for that Work Cat as seen above is the J column 2.20
Once you have the Work Cat unit cost then mulitply by the # of those rooms in the Sheet 1 G column in this case 1 
Then mulitple by the corresponding room SF from Sheet 1 D since the first one in this series is 13 it would be 110 
So for sheet 2 the product is 2.2 x 1 x 110 = 242.29

the next step is doing this for all rooms shown in sheet 1


----------



## Bartneedshelp (Dec 19, 2022)

Z51 said:


> What does your current data look like in your worksheet?  (How is the data entered into cells, e.g. what's in A1, B1, C1, D1, A2, B2, C2, etc.?)  And why do you think SUMPRODUCT is needed?



In an attempt to explain further.....

I am trying to figure out the total for each Work Category example is (01.02 - GENERAL REQUIREMENTS) in order to do that
Each room has a specific cost for that Work Cat as seen above is the J column 2.20
Once you have the Work Cat unit cost then mulitply by the # of those rooms in the Sheet 1 G column in this case 1
Then mulitple by the corresponding room SF from Sheet 1 D since the first one in this series is 13 it would be 110
So for sheet 2 the product is 2.2 x 1 x 110 = 242.29

the next step is doing this for all rooms shown in sheet 1

also see below


----------



## Bartneedshelp (Dec 19, 2022)

Bartneedshelp said:


> In an attempt to explain further.....
> 
> I am trying to figure out the total for each Work Category example is (01.02 - GENERAL REQUIREMENTS) in order to do that
> Each room has a specific cost for that Work Cat as seen above is the J column 2.20
> ...


im almost there... i was definilty overcomplicating things at first how do i combine these so that its ARRAY 1 X ARRAY 2 X ARRAY 3

Array 1 & 2 



Array 3 with conditions


----------

