-1

I have been working on this problem for a week, where I was looking for the totals of specific row items in a range multiplied by a factor. The formula below achieves the desired outcome. I need help structuring it in an arrayformula to accept multiple columns.

This formula works as is and with an IF statement. =ARRAYFORMULA(IFERROR(VLOOKUP(Sheet4!$A$1:$A$49,'MHC System Material QTY'!$A$16:$N$64,4,0),"")*'MHC System Material QTY'!$D$3)

This gives an error (IFS expects all arguments after 0 to be pairs) =ARRAYFORMULA(IFS('MHC System Material QTY'!$D$3>0,IFERROR(VLOOKUP('Lookup Table '!$A$2:$A$50,'MHC System Material QTY'!$A$16:$N$64,4,0),"")'MHC System Material QTY'!$D$3,'MHC System Material QTY'!$F$3>0,IFERROR(VLOOKUP('Lookup Table '!$A$2:$A$50,'MHC System Material QTY'!$A$16:$N$64,6,0),"")'MHC System Material QTY'!$F$3,""))

The goal is to get the sum total of product line items by row for column D,F,H,J,L,N in MHC System Material QTY tab. Vlookup solves the problem of what items to look for. The sum totals then need to be multiplied by D3,F3,H3,J4,L4,N4 to achieve the sum total fot total order volumes. Column J,L,N are battery option 2 in Lookup table..

Here is the link to sheet. Formula is in column C Total MHC Volume Forecast https://docs.google.com/spreadsheets/d/1etFlYSVPDJXZFOHWZaLFgQ-KDbulV3WWmYfCk0CVvdk/edit?usp=sharing

Your help is appreciated.

1

2 Answers 2

1

Welcome! It seems to me that you are completely unnecessarily complicating the task with additional checks using IF (). In fact, you only need one condition: whether a particular row in 'MHC System Material QTY'!$A$16:$A$72 refers to the corresponding position in 'Lookup Table '. Simply multiply each Cost column (columns E,G,I,K,M,O) by their coefficients ($D$3,$F$3,$H$3,$J$3,$L$3,$N$3), sum it and select the result from the desired row. For example, like this:

=SUMPRODUCT('MHC System Material QTY'!$D$3*'MHC System Material QTY'!$E$16:$E$72
  +'MHC System Material QTY'!$F$3*'MHC System Material QTY'!$G$16:$G$72
  +'MHC System Material QTY'!$H$3*'MHC System Material QTY'!$I$16:$I$72
  +'MHC System Material QTY'!$J$3*'MHC System Material QTY'!$K$16:$K$72
  +'MHC System Material QTY'!$L$3*'MHC System Material QTY'!$M$16:$M$72
  +'MHC System Material QTY'!$N$3*'MHC System Material QTY'!$O$16:$O$72,
'MHC System Material QTY'!$A$16:$A$72='Lookup Table '!A2)
0
0

with @JohnSUN help with sumproduct i created this formula which works 100%

=IF(OR('MHC System Material QTY'!$A16='Lookup Table '!$A2,'MHC System Material QTY'!$A16='Lookup Table '!$B2),SUMPRODUCT('MHC System Material QTY'!$D16*'MHC System Material QTY'!$D$3+'MHC System Material QTY'!$F16*'MHC System Material QTY'!$F$3+'MHC System Material QTY'!$H16*'MHC System Material QTY'!$H$3+'MHC System Material QTY'!$J16*'MHC System Material QTY'!$J$4+'MHC System Material QTY'!$L16*'MHC System Material QTY'!$L$4+'MHC System Material QTY'!$N16*'MHC System Material QTY'!$N$4),0)

I now need help putting it into an arrayformula and I not sure how to change the OR operator which does not work in google sheets arrayformula.

Thanks

4
  • Copy of How to use or in an arrayformula? Oct 22, 2022 at 15:45
  • Please ask only one question per post. Oct 22, 2022 at 15:46
  • And what is the purpose of SUMPRODUCT() in this formula? The sum of the products of which arrays is calculated by this function here? I don't see any array there.
    – JohnSUN
    Oct 22, 2022 at 18:01
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Oct 24, 2022 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.