Forum Discussion
Sum of a column based on name and date entered on another tab
I am working with two tabs. Billing and Totals.
On the Billing tab - Column B is the date, Column G is the name (Sue), and the number to be summed is in column F.
On the Totals tab, B3 is 1/1/2024, C3 is 2/1/2024, and so on, ending at M3 12/1/2024.
"Sue" appears in the static position of A4 on the totals tab.
I need a formula on the Totals tab that will look at the Billing tab and give me the sum of the numbers entered into column F, IF the name in G is "Sue", AND the date in B is the specified month. The formula will be copied over for each month and for each name. (I can make the changes to point at the right month and name once I have the actual formula to work with).
Hopefully this makes sense to someone. It barely makes sense to me.
2 Replies
There are lots of options but assuming you have 365 or at least Excel 2016 or newer you should be able to use SUMIFS:
=SUMIFS('Billing'!$F$1:$F$100, 'Billing'!$B$1:$B$100, ">="&B$3, 'Billing'!$B$1:$B$100, "<="&EOMONTH(B$3,0), 'Billing'!$G$1:$G$100, $A$4)
but better yet would be if you 'Format as a Table' the data on the Billing tab (and name the table 'Billing') so it might look more like:
=SUMIFS(Billing[Value], Billing[Date], ">="&B$3, Billing[Date], "<="&EOMONTH(B$3,0), Billing[Name], $A$4)So 'Format as Table' can be found on the home tab, just click in the data area or highlight all the data and click that button and it suggest the area and make sure to include headers and select the formatting. Then the 'TABLE' tab will come up and on the left side you can enter a name for the table. Then to refer to a all the data in a particular column of the data Table you use the table Name followed by bracket of the column name like BILLING[name] to return the column of the table listed under 'name'. Makes reading the formulas much easier and will include all the data in the table without referencing the entire column of the data sheet.
- Ahh, if only I could format as a table. It's not mine to change. But the formula that you gave at the start of your response was dead on. Works perfectly. Thank you so much for your quick reply. I appreciate your time.
