Forum Discussion

Chris2023's avatar
Chris2023
Copper Contributor
Jun 08, 2023
Solved

Sum With Multiple Arrays

I need to sum a column of data where the combination of two separate criteria are true. In the example below, I need to sum all the vehicles by month based on the city they were manufactured. 
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jun 08, 2023

    Chris2023 

    =TRANSPOSE(MMULT(TRANSPOSE($B$4:$D$6),IF($H$4:$J$6="False",0,1)))

    You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 08, 2023

Chris2023 

 

Variant of OliverScheurich's option assuming H4:J6 are real TRUEs/FALSEs:

=TRANSPOSE(MMULT(TRANSPOSE(B4:D6),--(H4:J6)))
Chris2023's avatar
Chris2023
Copper Contributor
Jun 08, 2023

Thank you so much @Quadruple_Pawn and Lorenzo! They are real TRUE/FALSE and that worked perfectly!!!

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 08, 2023

    Chris2023 

    They are real TRUE/FALSE. Simplfied version:

    =MMULT(TRANSPOSE(--H4:J6),B4:D6)
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 08, 2023

    Chris2023 

    Glad this works & Thanks for providing feedback

    To help people who Search who you mind marking OliverScheurich option as solution please?

    Thanks & Nice day...

Resources