SUMIF dates return wrong value

I have a simple equipment inspection schedule like this:

A B C D E F G
Equipment Time(Min.) Day 1 Next day Next day Next day Next day
Chiller 10.00 2018-07-01 2018-07-05 (=C+4) 2018-07-09 2018-07-13 2018-07-17
Tank 2.00 2018-06-30 2018-07-30 (=C+30) 2018-08-29 2018-09-28 2018-10-28
Boiler 1 5.00 2018-06-29 2018-06-30 (=C+1) 2018-07-01 2018-07-02 2018-07-03
Boiler 2 5.00 2018-06-29 2018-06-30 (=C+1) 2018-07-01 2018-07-02 2018-07-03

Column C is manually entered dates. D to G are formulas e.g C+4, E+4 etc.

I have a SUMIF formula looking for totals for a certain date. The range is columns C to G, criteria is a date, sum range = column B.

=SUMIF($C$2:$G$5,A9,$B$2:$B$5)

The values return aren't quite right:

2018-06-29 2018-06-30 2018-07-01 2018-07-02 2018-07-03 2018-07-04 2018-07-05 (Criteria)
10.00 4.00 86570.00 86562.00 86564.00 86566.00 86576.00 (Sumif results)

As you can see, only the 2018-06-29 returns the correct value. The rest are way off. All the dates are serial dates.

I have attached the Excel file here.

What did I do wrong? Can someone please help?

Thanks very much!

The sum range and criteria range both have to be of equal size. Even though You have only specified column B as the sum range, the formula compares the values in column B to the dates in column D, the values in column C to the dates in column E and so on.

Here's a workaround method that will give you the correct results.

=SUMPRODUCT(SUMIF(OFFSET($D$2,0,COLUMN($D$2:$Q$2)-COLUMN($D$2),ROWS($D$2:$D$5),1),A9,$B$2:$B$5))

Thanks very much jason.b75! SUMPRODUCT works!!!

Nice one bebo!

Maybe I should have tried the simple solution before dismissing it and posting a convoluted one.

For some reason I had it in mind that the simple version would only count 1 correct result per row regardless of the number of occurrences

Thank you