Calculating Amount of Requests per Month

fennerb

Hello,

I'm trying to calculate the amount of requests (using the AFID column) that our department gets on a monthly basis so that I can track them on a dashboard graph. For the month of January 2019, I have the formula:

=COUNTIFS({AFID}, AND(MONTH(@cell) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), YEAR(@cell) = IF(MONTH(TODAY()) = 1, YEAR(TODAY()) = 1, YEAR(TODAY()), {AFID})))

I keep getting "INCORRECT ARGUMENT." I think I might have an extra function in there?

I also am trying to track 2018, but started with 2019 first.

Paul Newcome

Try this...

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2019))

fennerb

It works! Thank you so much!

Paul Newcome

There it is! It was a mistake in the formula I gave you. Ugh. My apologies.

Change the less than symbol to a greater than symbol...

Sorry about that. Fat fingers and a small keyboard don't always work together.

Paul Newcome

Try this...

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2019))

fennerb

It works! Thank you so much!

Paul Newcome

Excellent. Happy to help! 👍️

fennerb

I forgot that we changed a few things on our sheet in the month of October. I'm now trying to add how many times "FOIA" shows up in the "Type of Request" column.

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 10, IFERROR(YEAR(@cell), 0) = 2019), AND(IF({Type of Request}), FIND("FOIA", @cell))

I added onto it, and it's coming back incorrect now. I'm sorry for the bother. Would you happen to be able to help with the addition, please?

Paul Newcome

Just a few tweaks...

AND is implied in the COUNTIFS function when you proceed to the next range/criteria set, so there is no need for that function there.

There is also no need for the IF function and associated parenthesis as that is also implied.

Finally, the FIND function generates a number as opposed to a true/false value, so you will need to specify that the number be greater than zero as the criteria.

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 10, IFERROR(YEAR(@cell), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) < 0)

Also keep in mind that FIND is case sensitive, so it will not count anything that could be "foia", "Foia", "FoIa", etc...

fennerb

The "Type of Request" is a drop-down only column, so I'm hoping there will be no issue with any case sensitivity.

I tried the formula, and it came back with "0." Prior to making this dashboard, I used Excel to dump and filter out all of the data. My number for October was 372 in my Excel table.

Paul Newcome

Can you copy/paste the exact formula you are using?

Also... Double check your cross sheet references to ensure they are covering the correct ranges.

Sometimes (when my computer is running a tad slow) I can click on a column header a little too quickly when the window pops up. Then the sheet that I am referencing actually loads, and the selection goes back to the cell in the first column of row 1. Then I have to go back and reselect the appropriate range. It doesn't happen very often, but it has happened to me before.

Another thing to do is to go to the source sheet and apply a filter that mimics the ranges and criteria set by the formula and see what appears. It may be that a data type is different in Excel vs SS.

fennerb

Yes, sir! Here it is.

=COUNTIFS({AFID}, AND(IFERROR(MONTH(@cell), 0) = 10, IFERROR(YEAR(@cell), 0) = 2019), {Type of Request}, FIND("FOIA", @cell) < 0)

Paul Newcome

There it is! It was a mistake in the formula I gave you. Ugh. My apologies.

Change the less than symbol to a greater than symbol...

Sorry about that. Fat fingers and a small keyboard don't always work together.

fennerb

IT WORKS!