2

I have some simple data in the following format:

enter image description here

I need to make a sum of the last column, but based on the text in Domain column. The resulting table should be like this...given the text filtering needed, I'm not using pivot tables.

enter image description here

To do this, for July Domain 1 for example, I have a formula like this:

=SUMIFS(C:C, MONTH(A:A), 7, B:B, "domain1.")

And for the rest of the domains:

=SUMIFS(C:C, MONTH(A:A), 7, B:B, "<>domain1.")

I am using SUMIFS because I need the multiple conditionals. But this formula above gives an error:

Error: Array arguments to SUMIFS are of different size.

The columns are all structured as A:A, B:B, etc. What is going wrong?

Thanks.

player0
  • 124,011
  • 12
  • 67
  • 124
Khom Nazid
  • 548
  • 1
  • 7
  • 20

1 Answers1

3
MONTH(A:A)

will output only one cell unless you wrap your formula into INDEX or ARRAYFORMULA:

=INDEX(SUMIFS(C:C, MONTH(A:A), 7, B:B, "<>domain1.*"))

enter image description here

=ARRAYFORMULA(QUERY({TEXT(A2:A, "mmmm"), B2:C}, 
 "select Col1,sum(Col3) 
  where not Col2 contains 'domain1.' 
    and Col3 is not null 
  group by Col1 
  label sum(Col3)''"))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you. The `ARRAYFORMULA` works! However, the text matching doesn't. Is that why you recommended the SQL query based approach? I'm also thinking I'll need to include the year in the conditions somehow. – Khom Nazid Aug 12 '20 at 14:04
  • 1
    for including year you can use proposed query formula and replace `mmmm` with `mmmm yyyy` – player0 Aug 12 '20 at 14:08
  • Oh, perfect. Yes the asterisk worked! Yay, thank you thank you! – Khom Nazid Aug 12 '20 at 14:14
  • Just for my knowledge, if I did want to create the kind of query format you suggested, how could I include the domain1 and the non-domain1 column using the same query? – Khom Nazid Aug 12 '20 at 14:14
  • 1
    not rly sure how to interpret your question... can you explain in more detail what are you after? – player0 Aug 12 '20 at 14:56
  • I figured it out. The asterisk is needed on both sides of a text to match, like SQL. – Khom Nazid Aug 25 '20 at 15:18