Help using SUMIFS with MONTH(TODAY())

Paul Newcome

Where is the original data stored? The data that is feeding your Consultant Monthly Revenue Sheet?

Brian Erdelyi

That data pulls from my Contracts and Billing-Active sheet and a Contracts and Billing-Archive sheet.

Then I sum those in my Consultant Revenue Sheet in the Total Revenu column (below).

Here are samples of the formulas I use to pull monthly revenu from Contracts and Billing-Active sheet and a Contracts and Billing-Archive sheet .

=SUMIFS({Contracts and Billing- Active Range 4}, {Contracts and Billing- Active Range 3}, "William Clayton", {Contracts and Billing- Active Range 2}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

=SUMIFS({Contracts and Billing- Archive Range 1}, {Contracts and Billing- Archive Range 2}, "Dean Waddell", {Contracts and Billing- Archive Range 3}, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))

Brian Erdelyi

Paul,

I should have mentioned the ranges are in those above formulas:

{Contracts and Billing- Active Range 4} is the invoiced revenue amount column

{Contracts and Billing- Active Range 4} is the team member name column

{Contracts and Billing- Active Range 2} is the date of revenue invoice column

Paul Newcome

My apologies. I had already gathered that much. I just didn't word my question properly.

How do those two sheets interact with each other? I assume one is current data only, but how is the Archive sheet populated?

Brian Erdelyi

Oh sorry. We enter all new jobs on the active sheet. Once the job is complete we insert the invoice date in the revenue invoice date column and we toggle the job status field for that row from "Active" to "Archive". This is done with a dropdown field. I have a wowrkflow that see the archive flag and moves the row from Active to Archive.

I scrape both the Active and Archive sheets just to be sure I dont miss anything if one of my users does something wonkey (i.e., forgets to toggle a job as complete).

Paul Newcome

Ok. I'm still not sure I see where the problem is...

To track the historical data, you would need to either reference the original data as outlined above where you specify the first day of the month etc etc, or the only other option would be to create a new date type column on the Consultant Revenue Sheet where you specify the last day of the month and set up a workflow to copy the rows to another sheet triggered on that date field later in the day and then reference that data in formulas to pull for metrics.