CODEWITHSUNDEEP
filtergoogle-sheetsmatchgoogle-sheets-formulagoogle-sheets-query
S31
S31

Reputation: 934

SUMIF with date range for specific column

I've been trying to find an answer for this, but haven't succeeded - I need to sum a column for a specified date range, as long as my rowname matches the reference sheet's column name.

i.e

Reference_Sheet

Date     John  Matt
07/01/19 1     2
07/02/19 1     2
07/03/19 2     1
07/04/19 1     1
07/05/19 3     3
07/06/19 1     2
07/07/19 1     1
07/08/19 5     9
07/09/19 9     2

Sheet1

     A     B     

  1        07/01
  2        07/07
  3        Week1 
  4  John  10
  5  Matt  12

Have to work in google sheets, and I tried using SUMPRODUCT which told me I can't multiply texts and I tried SUMIFS which let me know I can't have different array arguments - failed efforts were similar to below,

=SUMIFS('Reference_Sheet'!B2:AO1000,'Reference_Sheet'!A1:AO1,"=A4",'Reference_Sheet'!A2:A1000,">=B1",'Reference_Sheet'!A2:A1000,"<=B2")

=SUMPRODUCT(('Reference_Sheet'!$A$2:$AO$1000)*('Reference_Sheet'!$A$2:$A$1000>=B$1)*('Reference_Sheet'!$A$2:$A$1000<=B$2)*('Reference_Sheet'!$A$1:$AO$1=$A4))

Upvotes: 0

Views: 97

Answers (2)

player0
player0

Reputation: 1

alternative:

=SUMPRODUCT(QUERY(TRANSPOSE(QUERY($A:$D, 
 "where A >= date '"&TEXT(F$1, "yyyy-mm-dd")&"'
    and A <= date '"&TEXT(F$2, "yyyy-mm-dd")&"'", 1)), 
 "where Col1 = '"&$E4&"'", 0))

0

Upvotes: 0

ADW
ADW

Reputation: 4247

This might work:

=sumifs(indirect("Reference_Sheet!"&address(2,match(A4,Reference_Sheet!A$1:AO$1,0))&":"&address(100,match(A4,Reference_Sheet!A$1:AO$1,0))),Reference_Sheet!A$2:A$100,">="&B$1,Reference_Sheet!A$2:A$100,"<="&B$2)

But you'll need to specify how many rows down you need it to go. In my formula, it looks down till 100 rows.

To change the number of rows, you need to change the number in three places:

  1. &address(100
  2. Reference_Sheet!A$2:A$100," ... in two places

To briefly explain what is going on:

  • look for the person's name in row 1 using match
  • Use address and indirect to build the address of cells to add
  • and then sumIfs() based on dates.

Upvotes: 1

Related Questions