Forum Discussion

BFK's avatar
BFK
Copper Contributor
Jul 02, 2025

Sumifs or other solution for both vertical & horizontal criteria

Hi all, I would like to sum values from a matrix of vertical critiera (e.g. names) and horizontal ones (dates as well as other criteria). I am not sure the sumifs formula can actually do this. So fa...
excel
Formulas and Functions
BFK's avatar
BFK
Copper Contributor
Jul 03, 2025

Thanks M for your explanation. I realised, I missed to explain, that the data is currently in the matrix and I want it in the following structure, but summed up, since currently (also in the matrix, there is double mentioning of names and dates possible (for the latter I hence also have the "plan"):

 

                   Jun 25      Jul 25 ...... 

Name 1

Name 2

Name 3

m_tarler's avatar
m_tarler
Bronze Contributor
Jul 07, 2025

I'm sorry but I don't understand what else you need.  Both Oliver and I provided solutions in the format where it summed the values for each combination.  maybe provide a sample sheet of data and show how it should be summed/tabulated.  if you can post here you can share it via cloud service like onedrive or google

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 07, 2025

    Perhaps source data looks like

    and expected answer

    isn't it?

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Jul 07, 2025

      well maybe?  I have attached your example data tables with an 'unpivot' and then repivot formulas:

       

      • djclements's avatar
        djclements
        Silver Contributor
        Jul 08, 2025

        GROUPBY on both axes is also a potential option here:

        =LET(
   Nz, LAMBDA(x,IF(ISBLANK(x),"",x)),
   y, TRANSPOSE(VSTACK(Nz(A3:F3),GROUPBY(Nz(A4:A8),B4:F8,SUM,3,0))),
   TRANSPOSE(GROUPBY(TAKE(y,,2),DROP(y,,2),SUM,3,0))
)

Resources