Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Sumif function based on date range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Sumif function based on date range

    I have a list of records say with an amount and a date. How can I analyse the list based on the date range i.e. all records between say date 1 and date 2 total x. I would like the total to be returned to a single cell

    Thanks,

    Brian.

  • #2
    Assuming the value is in A1:A100 and the dates are in B1:B100

    =SUMPRODUCT((B1:B100>=DATEVALUE("1/1/2004"))*(B1:B100<=DATEVALUE("31/1/2004")),A1:A100)

    will return the sum of the values fro January 2004
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      Hi Brian

      Here is one way

      =SUMPRODUCT(($A$2:$A$100>=D1)*($A$2:$A$100<=D2)*($B$2:$B$100))

      Where $A$1:$A$100 has the dates and $B$1:$B$100 the numbers to SUM. D1= date1 and D2 = date2

      Or, use the more efficient DSUM like

      =DSUM($A$1:$B$100,2,$E$1:$F$2)

      Where A1 is the Date heading
      E1 and F1 has the exact same heading as A1
      E2 has >=11/11/04
      F2 has <=12/12/04

      Comment


      • #4
        Date imported with ' before it - Excel can't read it

        I have dates imported in this format say '01/01/2004
        Excel can't then read the cell. Ive tried replacing the ' but Excel can't dind it. Should be a simple problem. Please help!

        Comment


        • #5
          Those are not dates & need converting first.

          Try running this code on the range containing the text-dates

          Sub Convert_To_Date()
          'converts a range of text dates to excel dates
          Dim cdata As Range, cell As Range
          Set cdata = Application. _
          InputBox("Select Your Range to Convert", Type:=8)
          For Each cell In cdata.Cells
          cell.Value = DateValue(cell.Value)
          Next cell
          End Sub
          Kind Regards, Will Riley

          LinkedIn: Will Riley

          Comment


          • #6
            You could also use

            =MID(A1,1,256)+0

            then Copy and Edit>Paste Special - Values over the top of the orginals. Then format as required.

            Comment


            • #7
              Date range with ' before it

              I'm not familiar with code at all and have no idea how to do it. Is there anyway in Excel that I can conver it. When I go into the individual cell and just delete the ' Excel can read it. I just need a quick way of doing this for a few thousand records. A macro perhaps? I don't know.

              Thanks,

              Brian.

              Comment


              • #8
                Brian, Will's method is a macro

                Push Alt+F11 and go to Insert>Module and paste in the code. Now go back to Excel and push Alt+F8 and selected Convert_To_Date and click Run

                No trying to be smart, but I would suggest changing your Assumed Experience: down from Well above average

                Comment


                • #9
                  That's what I gave you.

                  OK, press ALT+F11 from your worksheet

                  This opens the Visual Basic Editor.

                  Click Insert|Module

                  Paste the code I gave you in the new module.

                  Then you can just run the macro from your worksheet using Tools|Macros.

                  Or, use Dave's formula based suggestion...
                  Kind Regards, Will Riley

                  LinkedIn: Will Riley

                  Comment


                  • #10
                    Try the macro or this

                    =DATEVALUE(CLEAN(A1))
                    Kind Regards, Will Riley

                    LinkedIn: Will Riley

                    Comment


                    • #11
                      Great thanks a lot guys.

                      Yeah will revise down my Assumed Experience to not a flippin' clue

                      Thanks a mill

                      Comment


                      • #12
                        Hi Brian

                        Sorry it should have been

                        =MID(A2,1,256)+0

                        Not

                        =MID(A2,2,256)+0

                        Comment


                        • #13
                          Simple Excel solution: (No macros)

                          If date (with leading ' ) is in cell A1

                          In cell B1
                          +RIGHT(A1,LEN(A1)-1)

                          Comment


                          • #14
                            DSum function

                            Dave,

                            The DSum function worked perfectly but I need to use it a number of times in the same sheet but it doesn't seem to be picking up any data. I have renamed the column heading to new names but it return a nil value

                            Comment


                            • #15
                              What have you got so far... any chance you could attach a sample of your data with the functions not working. My guees is that you haven't got the "criteria" set up correctly.

                              A quick look at the file will confirm it though
                              Kind Regards, Will Riley

                              LinkedIn: Will Riley

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X