Forum Discussion

miraci's avatar
miraci
Copper Contributor
Dec 10, 2025

Small business

I'm using Exel 365. I've started a small business and doing the bookkeeping. I've started a spread sheet with several columns for the products.

The first column has the daily date. on a second sheet I would like to get the totals for each month, I've used this formula:

=SUMIFS('Business'!$B$2:$B$1000, 'Business'!$A$2:$A:$1000, ">="&A2, 'Business'!$A$2:$A$1000, "<="&EOMONTH(A2,0))

This only gives me a 0 return on my second column (JAN) and not the totals of the first sheet second column.

Any help would be very much appreciated.

 

 

 

3 Replies

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Dec 13, 2025

    I suspect things are not all numbers.  "JAN-00" could be the date value of 2000-01-01 formatted as Month-Year or could be just TEXT.  you need to make sure those are all number / date values and not just text.  Make sure the cell is formatted as 'GENERAL' or 'NUMBER' of 'DATE' and then enter the full date like 1/1/2026.  It may and should then change to show the date in which ever is the default format but that us good and you can choose a different Date format to display as under the number formatting options on the home menu:

     

  • miraci's avatar
    miraci
    Copper Contributor
    Dec 12, 2025

    Hello Surya Narayana, first thank you for responding to my question.

    I still have a problem with the formulas. The original formula  still returns the wrong entry

    such as (JAN-00), the second formula returns an entry (#VALUE!) I've tried to change the 

    date format in every way but, the result is the same.

    Thank you

  • Surya_Narayana's avatar
    Surya_Narayana
    MCT
    Dec 11, 2025

    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.

     

Resources