I need help creating a formula to capture Monthly, Quarterly and Yearly submissions

ravilla

ravilla

for different individuals on the same sheet if possible

kowal

hi @ravilla,

I would create 4 helping columns (that will be functions) that would capture month, year, quarter from the column Date Created so it would look like =month([Date Created]@row), =year([Date Created]@row) for quarter you can use nested if function showing that if month is =if(OR(month=1, month=2, month=3), "Q1", if(OR(month=4, month=5, month=6), "Q2… etc.) based on that you will have three columns that will show you for which month, quarter and year the submission was created.

Hope it helps.

ravilla

@kowal thank you for the information. I apoligize i need to be a little more clear. The sheet i am being asked to create is a metric sheet that is separate from the original sheet.

kowal

hi @ravilla,

yes so you can reference another sheet in those formulas.

ravilla

@kowal

I've tried using this formula

=COUNTIFS({Support Staff WORK Sheet Range 1}{Support Staff WORK Sheet Range 2}, "John Doe", MONTH(@cell ),0 =1)

as I am trying to count the number of submissions that were done on a specific month by a specific individual on to my metric sheet.

Range 1 is column stating Created Date and Range 2 is column stating Requestor Name

kowal

hi @ravilla,

is the syntax of your =countifs proper? from what I know it shall be =countifs(range, condtion, range, condition) i can see you have two ranges first, then condition… have you tried building a simpler formula first that with one condition … and then modify it to have two conditions etc. ?

kowal

hello @ravilla,

I think you booked a call with me yesterday (Raquel Ravilla) through my booking link - then it got cancelled.

Is there anything I can help you with?