=SUM(MAXIFS formula works in some cells and not others

garymon9AX · #1 02-27-2018, 09:53 AM

Please help - this is driving me mad. See attached spreadsheet.

Column J in the 'Network Homes' sheet, is trying to return the latest date from Column C on the 'Job Occurrences' sheet, according to certain criteria in the formula.

Can any one tell me why the cells shaded pink in column J produce silly dates when others in the same column work ok?

I am somewhat of a novice, so would appreciate any help given.

NBVC · #2 02-27-2018, 11:21 AM

I think you are summing the max date of each of the items that you are searching column I for... Try using Max instead of Sum

=MAX(MAXIFS('Job occurrences'!C:C,'Job occurrences'!B:B,"s"&A42,'Job occurrences'!I:I,{"Visit Completed","No Access","Action/Info/Appt Reqd","Further Appt Reqd","Job Completed","On Site"}))

garymon9AX · #3 02-28-2018, 09:54 AM

Thanks NBVC. Your suggestion worked a treat.
I am most grateful.

Quote:

Originally Posted by NBVC

I think you are summing the max date of each of the items that you are searching column I for... Try using Max instead of Sum

=MAX(MAXIFS('Job occurrences'!C:C,'Job occurrences'!B:B,"s"&A42,'Job occurrences'!I:I,{"Visit Completed","No Access","Action/Info/Appt Reqd","Further Appt Reqd","Job Completed","On Site"}))

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula result not displaying in cell (but f9 works)	nath1235	Excel	4	10-06-2017 12:42 PM
Help with a formula which no longer works.	Silkwood	Excel	2	03-21-2017 02:25 PM
Syntax error on formula that works in spreadsheet	Mxthomp	Excel Programming	1	11-02-2016 06:49 PM
Formula works in Excel on my computer, but not on my PDA	Frenchy305	Excel	3	12-17-2015 08:33 AM
Formula works on most cells	gbaker	Excel	1	04-26-2014 12:31 AM