SUMIFS Help - returning 0.00 instead of the necessary total

Formulas really challenge me, I see them as mathematical and I'm terrible at math! I have researched and tried numerous different variations of this formula and none of them are working.

In the attached spreadsheet there are two versions listed of the formula I am working on:

=SUMIFS('1 Data'!$H:$H,'1 Data'!F:F,'Monthly 2015'!A20,'1 Data'!$E:$E,">='Monthly 2015'!C17",'1 Data'!E:E,"<=,'Monthly 2015'!C18")

The above formula is located in C20, the variation is in D20 - neither work, and they should both return a 2.0 in their respective cells. I only need one that works for all of the cells highlighted in burnt orange.

Using the data in the 1 Data Sheet I am trying to sum up the total hours for the client listed in A between two dates (C17 and C18), then go to the next cell (D20) and do the same thing for the next set of dates (weekly dates).

I've been doing this by grouping on the data page (a data page for each week) and then =cell with weekly total for that client - VERY MANUAL and TEDIOUS!

I want to make it a little more automated, I'll still need to do some manual stuff, but if it can enter each week's totals on it's own, that would be great. I've spent several hours researching and trying to figure out what I did wrong - can someone help?

The report format is the format I HAVE to turn in so I cannot change the format....row 17 will actually be formatted so that you cannot see the dates when printed - I only added them because changing row 18 to a date range was not working either), but row 18 does need the dates, it's part of the report.

The data format is the way the data comes out of my software and I cannot change it either.

I would also LOVE to be able to total the dollar amount (C37-G37) for each week from '1 Data'!I:I (G is used 4 times a year when I have 5 weeks in the billing cycle)

Any assistance would be GREATLY appreciated

gmr4evr1 - I thought that too and at one point changed the cells in rows 17 and 18 to date / time format - and it still didn't work....at one time it did give me the total over all hours, but not the total for the specific week....probably all a result of what ChemistB pointed out, of the hours being text and not numbers.

ChemistB - Issue One - I tried that solution and it wasn't working either, probably because of Issue Two.....it's funny that changing column E format to be numbers, it still didn't work - I had to do the solution in column J....very interesting. I'm going to apply this to my master report and see what the outcome is. Will let you know. Thanks!

That makes sense. Ok, the formula is working, with the exception of typos that have to be fixed between the data and the report.

Would I basically do the same thing for the dollars without the client name criteria (row 95 on the Monthly 2015 worksheet)? Again, would probably have to convert it to numbers because they are probably text too....

=SUMIFS('1 Data'!$H:$H,'1 Data'!$K:$K,">="&'Monthly 2015'!C$17,'1 Data'!$K:$K,"<="&'Monthly 2015'!C$18)

Yes?

Nope, figured it out...just changed H to I works great.....

Thank you so very much for your assistance ChemistB

BLERG

The formula is not adding the content when the date is equal to the date in C19......>= should take care of that, right?!

Probably because the values in column J of 1 Data also contain a TIME value.
But the Dates you are comparing them to (C18) do NOT have the time values.

So if you have say 11/1/2015 15:00:00 in Column J
That is NOT considered <= 11/1/2015

Try adding 1 to the date in C and using just <

=SUMIFS('1 Data'!$H:$H,'1 Data'!$K:$K,">="&'Monthly 2015'!C$17,'1 Data'!$K:$K,"<"&'Monthly 2015'!C$18+1)

Jonmo1 - I knew that time was going to get me at some point.....unfortunately the data out of my software can't be changed, so I am stuck with the time. Adding the +1 worked wonders - didn't seem to matter much if it was < or <=.....at some point it probably will, eh?

Thanks for your assistance!

Carol

You're welcome.

Originally Posted by CHillFL

didn't seem to matter much if it was < or <=.....at some point it probably will, eh?

Yes, it will matter.
If you ever happen to have a date in column J that has no time or happens to be exactly midnight.
Then it could be erroneousely counted.

That's what I figured - I will make sure they are all <

Thanks again!