Formulas stopped working on spreadsheet! Help!

[Garth2014]

Hi, I made a spreadsheet to track costs. Its been working fine for months but has randomly stopped woring. Its been driving me crazy for 3 days now. I can't see the issue! Please help before i lose my mind!

I have uploaded the file

Thanks,
Gareth

[Special-K]

Your dates, you cant say this

=SUMIFS(Purchase!$H$7:$H$631,Purchase!$G$7:$G$631,"CAPEX",Purchase!$I$7:$I$631,">=01-Apr",Purchase!$I$7:$I$631,"<=30-Apr")

01-Apr is a string but the value in Purchase! column I are dates, ie numbers.

Since 01-Apr is a string so is 01-Jan.
But you'll find 01-Jan is greater than 01-Apr yet Jan comes before Apr

You should correct ALL your formulas to this, changing the month where necessary.

=SUMIFS(Purchase!$H$7:$H$631,Purchase!$G$7:$G$631,"CAPEX",Purchase!$I$7:$I$631,">="&DATEVALUE("01/04/2017"),Purchase!$I$7:$I$631,"<=DATEVALUE("30/04/2017"))

[CAntosh]

Your dates look dodgy to me. Try specific dates or variables that produce dates. I went with the following in D5:

=COUNTIFS(Purchase!$G$7:$G$631,"CAPEX",Purchase!$I$7:$I$631,">="&$C$2,Purchase!$I$7:$I$631,"<="&EOMONTH($C$2,0))

Better yet:

=COUNTIFS(Purchase!$G$7:$G$631,$C5,Purchase!$I$7:$I$631,">="&$C$2,Purchase!$I$7:$I$631,"<="&EOMONTH($C$2,0))

[JBeaucaire]

In D5 and copied down that section:
=COUNTIFS(Purchase!$G$7:$G$631,$C5,Purchase!$I$7:$I$631,">="&$C$2,Purchase!$I$7:$I$631,"<="&EOMONTH($C$2,0))

Now adjust that starting formula for the top cell of each section, then copy down within the section.


The main problem is your dates in the original formulas, but this solves other small issues as well.

[Garth2014]

Hi Guys,

Thanks for that, the COUNTIFS are working great. Have adapted the formula for the SUMIFS and thats working for April but May and beyond are working but as a running total (ie adding April to Mays etc.) Im having a brain fart of a day on this, cant see teh wood for the trees

=SUMIFS(Purchase!$H$7:$H$631,Purchase!$G$7:$G$631,"CAPEX",Purchase!$I$7:$I$631,"<="&EOMONTH($G$2,0))

Thanks,
Gareth

[CAntosh]

It looks like you're missing the 'greater than' term, so it's just giving you everything prior to May 31. Try the following in I5:

=SUMIFS(Purchase!$H$7:$H$631,Purchase!$G$7:$G$631,G5,Purchase!$I$7:$I$631,">="&$G$2,Purchase!$I$7:$I$631,"<="&EOMONTH($G$2,0))

or:

=SUMIFS(Purchase!$H$7:$H$631,Purchase!$G$7:$G$631,"CAPEX",Purchase!$I$7:$I$631,">="&$G$2,Purchase!$I$7:$I$631,"<="&EOMONTH($G$2,0))

[Garth2014]

All sorted. Thanks to everyone!