CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaformula
ReggyB
ReggyB

Reputation: 75

How can I make it so that the column only calculates data from a certain date?

I am attempting to get the column to essentially lookup any submissions and only take into account the ones that apply to the numbers in column B. If any of the form submissions match the number in the respective row in Column B then I want it to essentially add the 1 to Column B. This is the formula that I've used but it doesn't seem to be working as I'd hoped it would.

=arrayformula(if(isnumber(B2:B150),countifs('Activity Logs'!E4:E,B2:B150,month('Activity Logs'!E4:E),month(edate(today(),-0)),year('Activity Logs'!E4:E),YEAR(edate(today(),-0))),))

Here's the spreadsheet: https://docs.google.com/spreadsheets/d/1T6TUzySQTLzjMni9ZyDWOFs6YnRqSwtzJ317ilJ0Y0Q/edit?usp=sharing

Upvotes: 1

Views: 34

Answers (1)

player0
player0

Reputation: 1

use in M9:

={""; ARRAYFORMULA(IFNA(VLOOKUP($B$10:$B, QUERY(
 FILTER('Activity Logs'!$E$5:$E, MONTH('Activity Logs'!$C$5:$C)=MONTH(M5&1)), 
 "select Col1,count(Col1) group by Col1"), 2, 0)))}

enter image description here

Upvotes: 1

Related Questions