Forum Discussion
Small business
hi miraci Try this version:
=SUMIFS(
'Business'!$B$2:$B$1000,
'Business'!$A$2:$A$1000, ">=" & A2,
'Business'!$A$2:$A$1000, "<=" & EOMONTH(A2, 0)
)
This now correctly:
- Sums column B on the Business sheet
- Only when column A contains dates within the month in A2
Your A2 Cell Must Contain a First-of-Month Date
Example:
A (Month Start)
Expected
1-Jan-2025
January total
1-Feb-2025
February total
If you put any date from that month (e.g., 15-Jan), it will still work — but it’s best practice to use the 1st of each month.
Even Better Formula (If You Want Simplicity)
If A2 contains a date within the target month, you can use:
=SUMPRODUCT(
(MONTH('Business'!$A$2:$A$1000)=MONTH(A2)) *
(YEAR('Business'!$A$2:$A$1000)=YEAR(A2)) *
'Business'!$B$2:$B$1000
)
This removes the need for >= and <= logic.
hi miraci Try this version:
=SUMIFS(
'Business'!$B$2:$B$1000,
'Business'!$A$2:$A$1000, ">=" & A2,
'Business'!$A$2:$A$1000, "<=" & EOMONTH(A2, 0)
)
This now correctly:
- Sums column B on the Business sheet
- Only when column A contains dates within the month in A2
Your A2 Cell Must Contain a First-of-Month Date
Example:
A (Month Start) | Expected |
1-Jan-2025 | January total |
1-Feb-2025 | February total |
If you put any date from that month (e.g., 15-Jan), it will still work — but it’s best practice to use the 1st of each month.
Even Better Formula (If You Want Simplicity)
If A2 contains a date within the target month, you can use:
=SUMPRODUCT(
(MONTH('Business'!$A$2:$A$1000)=MONTH(A2)) *
(YEAR('Business'!$A$2:$A$1000)=YEAR(A2)) *
'Business'!$B$2:$B$1000
)
This removes the need for >= and <= logic.