Summing values based on multiple criteria. Columns are different sizes

0
votes

I have a Google Sheet (Test Sheet 2) with two sheets in it, Sheet 1 and Sheet 2. Sheet 2 is where all of the data is and I need to get the sums of the counts of that column based on three criteria into sheet 1 column C. The name, week, year and count need to match up. I used the formula 

=arrayformula(iferror(vlookup(A2:A&2020&B2:B, {Sheet2!A2:A&Sheet2!B2:B&Sheet2!C2:C, Sheet2!D2:D}, 2, FALSE)))

but that only works for unique rows. In the example sheet I am providing, the formula works well for Bill, Lisa, Katie and Jon because they all have one value for 'count' from Sheet2 when the parameters of name, week and year match up. But Mike has two rows matching the criteria. This formula returns the first match which is 3. The other value is 4 so I would like the count in Sheet1 to show 7 instead of 3. I need it to add them up.

I also tried to use sumifs but the columns are two different sizes so that didn't work.

Any idea? I did try to combine sumif with the above formula but that did not work either.

Link to Test Sheet

2
google-sheetsgoogle-sheets-formula
Hello, what do you mean the columns are two different sizes? Can you give an example of this? – Alessandro
yeah sure. One of the formulas I try using is =sumifs(Sheet2!D2:D, A2:A = Sheet2!A2:A, Sheet2!B2:B = 2020, B2:B, Sheet2!C2:C) but I get the error "Array arguments to SUMIFS are of different size." because the array columns in Sheet 2 are 6 rows while the columns in Sheet1 are 5 rows. – Tarik Hodzic

2 Answers

1
votes

Try this query() 

=query(Sheet2!A:D, "Select A, C, sum(D) where B = 2020 group by A, C label C 'Week', sum(D) 'Count'", 1)

UPDATED:

If you really need to use vlookup in arrayformula() you can always ise the query (that deals with the summing) as the lookup range. In the spreadsheet I used

=ArrayFormula(if(len(A2:A), iferror(vlookup(A2:A&year(D2:D)&E2:E, query({Sheet2!A:A&Sheet2!B:B&Sheet2!C:C, Sheet2!D:D}, "Select Col1, sum(Col2) where Col1 <>'' group by Col1", 1), 2, 0)),) )

and see if that helps?

enter image description here

1
votes

Solution with ARRAYFORMULA and SUMIF

If you need to use ARRAYFORMULA to improve the performances you can use this tweak of the SUMIF statement: basically you can concatenate the conditions to make them create a single AND condition.

This would be a possible solution for the formula in your comment:

=ARRAYFORMULA(SUMIF(Sheet2!A2:A&Sheet2!B2:B,A2:A&2020,Sheet2!D2:D))

Solution with SUMIFS

If you are looking for a solution with the SUMIFS formula you can use this:

SUMIFS('sum_range', 'criteria_range', condition, ['criteria_range_2', condition_2])

In your case this will translate to:

=SUMIFS(Sheet2!D2:D, Sheet2!A2:A, A2, Sheet2!B2:B, 2020)

In this case the ranges dimensions won't affect the formula execution. Just drag this formula for the Sheet1 table column and you will get the results. The drawback is that you cannot use ARRAYFORMULA with SUMIFS. Performance wise, if you have a lot of rows in the Sheet1 I suggest using the ARRAYFORMULA solution, since this will trigger a lot of formula calls instead of just one.