Wednesday, April 11, 2018

yogi_Sum Numbers In Column B if entry in column C matches one in Lookup table when dare in column A is within specified limits

Google Spreadsheet   Post  #2420

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Apr-11-2018
SUMIFS on whole column with multiple criteria including conditionals
Here is an example spreadsheet. I wish to sum the values in column B when the date is between 2018-04-08 and 2018-04-20 AND the letter in column C is in column the lookup table
2018-04-101aLookup table
2018-04-0910ra
2018-04-26100gb
2018-04-021000ec
2018-04-1210000vd
e
0
https://docs.google.com/spreadsheets/d/10ueiZnLRYsd5-65xtlMIPVp0Tya73g8dfjF8rT02Ufg/edit?usp=sharing


the following works in excel
{=sum(sumifs($B:$B,$C:$C, E2:E6,$A:$A,"<=" & DATE(2018,4,20),$A:$A,">="& DATE(2018,4,8)))}

but this doesn't work in Sheets due to sumifs not working in arrayformula
=ARRAYFORMULA(sum(sumifs($B:$B,$C:$C, E2:E6,$A:$A,"<=" & DATE(2018,4,20),$A:$A,">="& DATE(2018,4,8))))
does anyone have any ideas as to how i might get the desired result?