SUMIFS With Text and Date Comparison-CodePudding

theoretically I want to sum the total income each employee made for all the businesses.

Like this:

Employee 1 = Biz 1 income Biz 2 income Biz 3 income, etc... Employee 2 = Biz 1 income Biz 2 income Biz 3 income, etc...

Technically and based on the table below, I want to sum a range in column R starting from cell R14 where the text in column W starting from W14 is the same in column P starting from cell P14 AND the name of the month in column V starting from cell V14 is equal to a month in date in column N starting from cell N14. * (I included the date because this is part of a budget planner so I need to categorize the data based on months.)*

I used this formula: =SUMIFS(R14:R1013, P14:P1013, U14:U1013, TEXT(N14:N1013,"MMMM"),"="&T14:T1013)

But it prompts me with the error: Array arguments to sumifs are of different size

What could be wrong here? Does someone have any idea?

Thanks for your help in advance!

CodePudding user response：

Try wrapping the text formula into ARRAYFORMULA to get the full column:

=SUMIFS(R14:R1013, P14:P1013, U14:U1013, ARRAYFORMULA(TEXT(N14:N1013,"MMMM")),"="&T14:T1013)

CodePudding user response：

You can get the totals for all months and all employees with query(), like this:

=arrayformula( 
  query( 
    { text(N13:N, "yyyy-MM"), O13:R }, 
    "select Col1, Col3, sum(Col5) 
     where Col3 is not not null 
     group by Col1, Col3", 
    1 
  ) 
)