CODEWITHSUNDEEP
google-sheetssumifs
Devu
Devu

Reputation: 451

Google Sheets SUMIF criteria - How to refer to the cell being evaluated?

Sum for the month

Refer sheet above. In column E, I need the sum of 'StockCount' for the month of July-18. The formula should look at all dates in column B (EventDate) and sum the stock count only if the event is in the same month as in column A.

I'm trying SumIf in different ways but perhaps this isn't the right function to use. Note that I don't want to add an extra column to the sheet.

Basically, I'd like something like if 'Cell being evaluated'-Cell A < 31 and >0, THEN add a stock count.

Upvotes: 0

Views: 3923

Answers (2)

player0
player0

Reputation: 1

paste this in E3 cell:

=ARRAYFORMULA(IF(A3:A<>"", SUMIF(DATE(YEAR(B3:B), MONTH(B3:B), 1), 
 DATE(20&RIGHT(A3:A, 2), TEXT(A3:A, "MM"), 1), C3:C), ))

or this in E2 cell:

={"sum per A column"; 
 ARRAYFORMULA(IF(A3:A<>"", SUMIF(DATE(YEAR(B3:B), MONTH(B3:B), 1), 
 DATE(20&RIGHT(A3:A, 2), TEXT(A3:A, "MM"), 1), C3:C), ))}

0

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34380

If column A contains a date which is the first day of each month, you can use this filled down from cell E3:

=ArrayFormula(if(A3<>"",sumif(date(year(B$3:B),month(B$3:B),1),A3,C$3:C),""))

(it has to be entered as an array formula to convert the whole of column B to dates).

or better

=if(A3<>"",sumifs(C$3:C,B$3:B, ">="&A3,B$3:B,"<="&eomonth(A3,0)),"")

But you can also use this self-expanding array formula in E3:

=ArrayFormula(if(A3:A<>"",sumif(date(year(B3:B),month(B3:B),1),A3:A,C3:C),""))

enter image description here

Upvotes: 1

Related Questions