sumif based on dates ignoring totals

  • Hi. Sorry to bother you guys again! :(
    I have this workbook in which Worksheet1 has around 25,000 rows of data that I have put in named ranges.
    Worksheet2 pulls that information and consolidates it into the name, date, count and other data (in columns). The rows are performances, with a total of each column after the eighth performance. The totals are directly beneath the data for the week...like, Column E is full paying reservations with a numeric value in rows 1 through 8 and a total of them in row 9.
    then, starting in row 11 the process is repeated for the next eight performances, with a total in row 19. This sheet has to remain formatted in that way.
    Worksheet3 pulls data into simply weekly totals. (easy)
    but I also would like to break the totals into a monthly value. I have tried many many approaches (many from this forum and may I interject that I love the new forum but I have run across some old posts where the workbook examples are no longer attached, and I understand that is just collateral damage, I only mention it now in my defense of not being able to figure this out)
    I just can't get around the "total rows" in anything I have tried; so when a month crosses from one week of performances to the next, it adds in the "totals" giving an inflated value for the month. OR I had this fun thing where it began adding again, but wiped out the previous month.
    Any ideas? I am VBA challanged and others viewing this info prefer to not use pivot tables.
    OF COURSE the data changes daily and the number of weeks/months will change every 6 to 10 weeks depending on the run of the show.
    (We use Excel 2003 on Win XP)
    Thanks to any who can help, and thanks for this forum, though this may not show it, I have learned a great deal!

    You can tell the quality of a person by how they treat people they don't need

  • Re: sumif based on dates ignoring totals


    ok, here it is. I didn't include any of the rough data on sheet1. It is huge.
    (I also changed the number values used in this example)
    the formulas on sheet two columns E through L are sumproduct formulas like this one =SUMPRODUCT((perf=$A7)*(type="C")*(fill="Dinner and Show"))+SUMPRODUCT((perf=$A7)*(type="D")*(fill="Dinner and Show"))+SUMPRODUCT((perf=$A7)*(type="K")*(fill="Dinner and Show"))+SUMPRODUCT((perf=$A7)*(type="N")*(fill="Dinner and Show"))
    but I have shown a numeric value in place of the formulas.
    Thanks so much for helping.

  • Re: sumif based on dates ignoring totals


    Hi Kimberly


    I must say that the correct way to do this would be via [pt]*[/pt]. I would strongly advise against caterinng to those with little to no Spreadsheet knowledge. If you simply used one sheet for raw data entry, a Pivot Table would give you want and much more.


    However, I know all too well how sometimes one is forced down the wrong path.


    I have include some use of the very efficient DSUM Function. I would strongly advise against the use of SUMPRODUCTS and Arrays with 25,000 rows of data. I would also create a [dr]*[/dr] for your table to use within the DSUM.


    You will note that I have hidden the criteria rows in the example and also included some Conditional Formatting for ease of reading.


    It is vital to take not of the relative range reference to the first cell in the Date Column of Sheet2, being used in the DSUM criteria. Take a peek at the excel help on the Database function as they are well worth learning.

  • Re: sumif based on dates ignoring totals


    OK, I did create the Dynamic named ranges in the raw data (easier than I thought it would be, thanks for the link).
    I tried to adapt your DSUM formula to my workbook and despite the fact that I have a way better understanding of it than before it isn't coming up with the correct totals. It is actually giving me totals that are less than one week for the entire month. Not sure what I did wrong there.
    I am trying to apply the DSUM in place of SUMPRODUCT on my sheet and I can't figure out how to use the Dynamic Named Ranges in it. I have 3 dynamic named ranges, 1 each in Columns A, B and C. (Sorry, reading the help file is on my agenda but unfortunately it is after working 2 jobs and home schooling 4 kids, so that puts me at about 2 AM here when my grasp of this is marginal at best) How do I make it recognize all 3 ranges?
    Thanks again, you're the best!

    You can tell the quality of a person by how they treat people they don't need

  • Re: sumif based on dates ignoring totals


    Hi Kimberly


    The Dynamic Named Range must be inclusive of all data in the Table. I have just modified my original attachment, re-download to see what I mean.

  • Re: sumif based on dates ignoring totals


    AWESOME! thanks so so much!
    (On the humorous side, when I got on here tonight and started the next course of your "download training" I found I am on level3--- :drum: which of course covers the Dfunctions..yea, that's me alright, always a day late and a dollar short!)
    I am rebuilding the workbook, if they won't let me use the pivot table I want it to be as stable as possible.
    Thanks again!

    You can tell the quality of a person by how they treat people they don't need

  • Re: sumif based on dates ignoring totals


    One thing to note about the Dynamic Range I used. I used the MATCH Function (within the OFFSET Function) to determine the last used row from Column D, not Column A. This is because Column A has " Total Week x" and the MATCH (MATCH("*",Sheet2!$A$2:$A$30000,-1)) was seeing the first cell with " Total Week x" as a Match due to the Space before the text. I'm really not too sure why as the "*" is a wildcard that represents and number of String characters. The MATCH is needed as there are blanks between your data :nono:

  • Re: sumif based on dates ignoring totals


    The blanks are there because my boss will print this daily and his goal is to match the look of the reports we got from our old reservation system so the conversion to the new system will be better received. This report now takes about 20 seconds to recalculate and twice Excel has said "not responding" but if you wait for about another 20 -30 seconds it starts up again. (I am trying to master the DSUM really fast now, so I may be back :? )
    Thanks!

    You can tell the quality of a person by how they treat people they don't need

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!