SUMIFS based off Status and date?

GWilkins86876

I am building a counter for a team where they do a simple web form to say which task they did, and how many times they did it. There then needs to be a dashboard that then has a sum of that type of tasks completed during the current month, and the previous month (seperate).

The FormData screenshot is the Grid that collects the submissions from the web form. The "Type" column is a list of dropdowns that describe the task type. The sum of "Count" would be how many of said task were completed. Time is the delivery time for the "Time Between Request and Delivery (Mins)" status.

The CounterForDash screenshot is the counter grid I was using to aggregate the data for this and other teams to then feed to a Dashboard of Metrics.



What I need is an equation for the "Metric This Month" and "Metric Last Month" that will SUM the Count for the first 2 Types and then an AVG the time for the 3rd Type for this month, and then last month.

 

This is what I was trying for the This Month - SUM

EquipmentType = Type

Equipment Date = System logged Creation Date

=SUMIFS({EquipmentType}, ="# of Equipment Cleaned/Decontaminated", MONTH(TODAY()), =MONTH({EquipmentDate}))



I am getting an #INVALID OPERATION error for some reason. I'd love to have a third condition do YEAR(TODAY())=YEAR({EquipmentDate}) too so that I never have to touch this again. I am not sure how to complete the previous month ?(have a MONTH()-1 and IF 0 then 12 and YEAR()-1)? or the average of time tasks.



Edit1:

I realize I hadn't included a full range at the beginning of the SUMIFS so I added that in the equation below, but still nothing. Now I get a #INCORRECT ARGUMENT

EquipmentTasks = Count Column (also tried entire grid)

=SUMIFS({EquipmentTasks}, {EquipmentType}, ="# of Equipment Cleaned/Decontaminated", MONTH(TODAY()), =MONTH({EquipmentDate}))



Edit2:

I was able to get a this month working for the SUM by having a separate Month and Year column in the FormData, then moving the EquipmentDate to EquipmentMonth and adding EquipmentYear and making them the range and the MONTH(TODAY()) or YEAR the condition. I still need to figure out the previous month and the average time for current and previous month. 

 

Edit3:

I was able to get the average time with a SUMIFS/COUNTIFS but the previous month is still eluding me.

Paul Newcome

=SUMIFS({EquipmentTasks}, {EquipmentType}, ="# of Equipment Cleaned/Decontaminated", MONTH(TODAY()), =MONTH({EquipmentDate}))

 

In this formula, you have the criteria before the range. Try something like this...

 

=SUMIFS({EquipmentTasks}, {EquipmentType}, ="# of Equipment Cleaned/Decontaminated", @{EquipmentDate}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

 

To account for the year without having to separate out the month and year into separate columns...

 

=SUMIFS({EquipmentTasks}, {EquipmentType}, ="# of Equipment Cleaned/Decontaminated", {EquipmentDate}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

 

To use the above for the previous month:

 

=SUMIFS({EquipmentTasks}, {EquipmentType}, ="# of Equipment Cleaned/Decontaminated", {EquipmentDate}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()) - 1, IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

GWilkins86876

Thank you for the response. I actually ended up solving previous month with a nested IF month flag in the Tasks sheet that also accounts for running the report in January (thus needing December of previous year). I then use this Y or N flag in the SUMIFS.

 

=IF(MONTH(TODAY()) - 1 = 0, (IF(YEAR(TODAY()) - 1 = YEAR(Created1), "Y", "N")), (IF(MONTH(TODAY()) - 1 = MONTH(Created1), (IF(YEAR(TODAY()) = YEAR(Created1), "Y", "N")), "N")))

Paul Newcome

Glad you were able to find a working solution.