Help using SUMIFS with MONTH(TODAY())

Brian Erdelyi

Help! I have gone over it several times and can't seem to find the error.

This formula sums the value of Williams jobs that were completed in the 4th billing quarter (Contracts and Billing- Active Range 1 calculates the billing quarter based on the calendar date)

=SUMIFS({Contracts and Billing- Active Range 4}, {Contracts and

Billing- Active Range 3}, "William Clayton", {Contracts and Billing-

Active Range 1}, "4")

I want to sum his jobs based on the current month rather than the billing quarter. When I change the second criterion range to  Contracts and Billing- Active Range 2  (which is my date column) and make my second criteria =MONTH(TODAY()) it returns a zero even when it should return a sum amount. I have confirmed that Contracts and Billing- Active Range 2  is formatted as a date column. I have not used the month(today() function before. Maybe this is where I am messing up? I pasted the formula that isn’t working below

=SUMIFS({Contracts and Billing- Active Range 4}, {Contracts and

Billing- Active Range 3}, "William Clayton", {Contracts and Billing-

Active Range 2}, =MONTH(TODAY()))

Any advice would be much appreciated! Thank you.

Paul Newcome

Make sure your ranges were all created correctly and are all referencing the same number of cells.

I have noticed that (sometimes) when creating a cross sheet reference, I will click on the column header a little too quickly before the sheet has a chance to completely load. Once it does completely load, the selection reverts back to the home cell (top left corner).

ron.judenberg112096

Try this

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

Paul Newcome

I am going to make 2 suggestions...

1. "@CELL" should actually be lowercase: "@cell"
2. If you have even a single blank cell or non-date value within your date range, this will break the entire formula. To account for this, you may want to consider using an IFERROR function.

=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()))

Brian Erdelyi

Thank you for your quick reply Paul. I tried your suggestionand its returning an "#invalid ref" (pic below)

Here is the formula in the cell (pic below). Contracts and Billing-Active Range 2 is the entire colum located in another sheet (not the sheet where I am doing the calculation). Just confirming the the @cell is still what I want to be using here? If so, any idea why its returning the invalid ref?

Paul Newcome

Make sure your ranges were all created correctly and are all referencing the same number of cells.

I have noticed that (sometimes) when creating a cross sheet reference, I will click on the column header a little too quickly before the sheet has a chance to completely load. Once it does completely load, the selection reverts back to the home cell (top left corner).

Brian Erdelyi

Great call! For some reasn the "Contracts and Billing-Active Range 2" was in the formula but it wasnt actually pointed at the other sheet. I deleted it and re-inserted the reference then it worked. Thank you so much for your help!

Paul Newcome

Happy to help! 👍️

Brian Erdelyi

Paul,

One more question for you. This formula that you helped me create tracks the billed revenue for each team member during the current month. I believe the bar graph will "reset" to show no revenue on the first day of the proceeding month since no revenue will have been billed in that month yet. I havent tested that theory yet but it makes logical sense.

My question, can you think of a clever way to capture each months revenu before it resets on the first of the month? If I can capture that point in time data before it resets then I can chart it in a seperate line chart to show each team members revenue over the course of the year.

I tried a couple of things but I havent been sucsessful yet. Let me know if you have any ideas.

Thank you,

Brian

Paul Newcome

There are a few different options. The best way I can think of would be to structure your sheet so that you have a date type column [Dates] on the far left. In this column you would simply enter the first day of every month starting in row 2.

The next column to the right would be a text/number type [Chart Labels]. In this column enter the below formula and dragfill down.

=MONTH(Dates@row) + "/" + RIGHT(YEAR(Dates@row), 2)

Then the next columns after that would be a column for each person. Make the column names the names of the people and then put each person's name in row 1.

Your sheet should now look something like this...

Then in the highlighted cell in the above screenshot [John Doe]2, you would enter this...

=SUMIFS({Contracts and Billing- Active Range 4}, {Contracts and Billing- Active Range 3}, [John Doe]$1, {Contracts and Billing-Active Range 2}, AND(IFERROR(MONTH(@cell), 0) = MONTH($Dates@row), IFERROR(YEAR(@cell), 0) = YEAR($Dates@row)))

Note the bold portions. We have replaced specific text with cell references. Also note the different locations of the $ to lock in either a row reference or column reference.

This will allow you to dragfill the formula across the columns and down the rows to quickly populate the data throughout the table without having to manually update the formula for each of the variables.

Then when you go to build the Chart for the dashboard, start your selection in [Chart Labels]2 and then grab the table.

Of course there are numerous charts you can choose from, but the basics would still be the same. The [Chart Labels] column will be the axis opposite the actual counts and the legend will be established based on the people.

Brian Erdelyi

Good morning Paul,

I formatted a new sheet (my Point In Time Revenue sheet) the way you recommended but when I drop in the main formula it is returning #UNPARSEABLE.

=SUMIFS({Contracts and Billing- Active Range 4}, {Contracts and Billing- Active Range 3},[William Clayton] $1, {Contracts and Billing-Active Range 2}, AND(IFERROR(MONTH(@cell), 0) = MONTH($Dates@row), IFERROR(YEAR(@cell), 0) = YEAR($Dates@row)))

I have been playing with the formula you created, making sure all the references are actually pointing back at the proper sheets when I realized I may have another issue.

I didn’t mention in our previous convo that I pull data from a Contracts and Billing-Active sheet and a Contracts and Billing-Archive sheet. After you helped me figure out how to calculate the dynamic revenue by month I created a sheet that calcs and sums all of the data from active and archive. That sheet (called Consultant Monthly Revenue) drives my dashboard widget. Here is a snapshot of that sheet below:

In the Consultant Monthly Revenue sheet there is a column called Total Revenue where the active and archive revenue amounts are already summed (see 2nd column above). Is there a way to just grab that amount for each consultant on the last day of the month (before the data resets to zero for the proceeding month)? That way I wouldn’t have to reference the active and archive source sheets at all and I wouldn’t have to do any redundant revenue calcs in my new Point In Time Revenue sheet.

I hope I am making sense and I'm sorry I didn’t mention the two source sheets earlier. I was trying to keep things simple in my initial explanation to you. 

Thank you, Brian

Paul Newcome

You have a space between the column name and the row reference for the [William Clayton] cell reference. Here is the formula with the space removed.

=SUMIFS({Contracts and Billing- Active Range 4}, {Contracts and Billing- Active Range 3},[William Clayton]$1, {Contracts and Billing-Active Range 2}, AND(IFERROR(MONTH(@cell), 0) = MONTH($Dates@row), IFERROR(YEAR(@cell), 0) = YEAR($Dates@row)))

I will read through the rest of your comment and get back to you. I just wanted to go ahead and get the formula corrected real quick.

Paul Newcome

I'm not sure I follow...

Are you just trying to capture the data or were you planning on using it for other metrics/dashboard widgets?

Brian Erdelyi

Ok I fixed the spacing but now I am getting an #INVALID REF.

Sorry I know this is getting confusing. I am planning on capturing the point in time (last day of the month revenue) in my sheet that I am calling Point In Time Revenue THEN driving a dashboard widget from that captured data/sheet

Brian Erdelyi

I can share my sheet if that would be helpful?

My current sheet called Consultant Monthly Revenue works well tracking each team members revenue for the current month. I am using that data to drive a dashboard bar chart.

The issue is March 1st that sheet will zero out because there will not have been any revenue billed in March yet. My associated dashboard chart will "reset" to show zero revenue and I will have lost the charted revenue from the previous month.

What I am asking for help with is to capture that previous month revenue in a seperate sheet (my Point In Time Revenue sheet)before it resets on the first day of the proceeding month. Then I can use that data to drive a new line chart widget that will track that end of month revenue for each month and it wont vanish on me.

Paul Newcome

Are you able to provide a screenshot of a manually entered/mocked up representation of what you want the final outcome to look like?

Brian Erdelyi

Paul,

Here is my existing Consultant Monthly Revenue sheet and the associated bar chart it is driving. This works well but it does not preserve any historical data. It refreshes each month.

This is the sheet I am trying to create now and the line chart that it will drive.

I have obviously mocked up/manually entered the revenue amounts here but I would like to populate that data automatically. That is where i am struggling. We are already calculating this data in my Consultant Monthly Revenue sheet, I just dont know how to transfer that to this new sheet before it dissapears at the end of the month.

This sheet will provide historical revenue data as the year progresses. In this example, you can see the monthly revenue for each team member over the course of the year. *If this was real data we would only see data for January so far and February would appear at the end of this month/beginning of March.

Brian