Tips on finding inconsistent formulas?

[bobby_smith]

Hi all,

I'm looking for suggestions on how to find inconsistent formulas when reviewing a worksheet.
Specifically inconsistent formulas in columns.

Lets say for example, I have twelve columns of data for January to December.
I have a separate column which should be summing range January to August, however one of the columns range is January to June and not August.
What will be the easiest way to identify this inconsistency when you have thousands of rows of data?
I used the show formula in the illustration below, however this will not be practical for thousand of row.

Thanks in advance.
I'm using Excel M365

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	February	March	April	May	June	July	August	September	October	November	December		Month to Date total
2	88	53	13	36	36	64	69	35	37	36	48		=SUM(A2:H2)
3	7	68	79	28	47	44	98	2	81	47	71		=SUM(A3:H3)
4	35	75	46	55	69	53	60	53	56	50	31		=SUM(A4:H4)
5	33	2	14	67	76	44	37	61	44	54	24		=SUM(A5:H5)
6	60	88	17	31	90	27	71	77	31	74	100		=SUM(A6:H6)
7	50	87	97	26	24	44	23	28	11	11	53		=SUM(A7:H7)
8	47	24	58	65	25	37	30	55	11	25	4		=SUM(A8:F8)
9	4	99	56	51	68	62	88	92	49	97	12		=SUM(A9:H9)
10	36	100	0	10	82	74	11	21	23	75	93		=SUM(A10:H10)
11	84	21	95	98	21	5	94	38	36	40	38		=SUM(A11:H11)
12	13	4	17	77	93	13	77	2	37	100	89		=SUM(A12:H12)
13	26	48	94	86	85	45	14	24	33	62	78		=SUM(A13:F13)
14	34	55	86	22	84	47	14	28	52	68	11		=SUM(A14:H14)
15	16	25	57	62	63	16	82	12	95	48	80		=SUM(A15:H15)
16	88	13	77	45	80	51	49	37	32	48	56		=SUM(A16:H16)
Sheet1

Cell Formulas
Range	Formula
N2:N7,N9:N12,N14:N16	N2	=SUM(A2:H2)
N8,N13	N8	=SUM(A8:F8)

 

[Toadstool]

Hi bobby_smith,

Formulas, Error checking will show that issue, although it also shows on my Excel that not including column M may be an issue.

When I develop a sheet I try and put cross-checks in place, so I'd check that the column N total matches a SUM of the expected cells and if not show an error message.

 

[bobby_smith]

Thanks Toadstool.

 

[KeepTrying]

Hi all,

I'm looking for suggestions on how to find inconsistent formulas when reviewing a worksheet.
Specifically inconsistent formulas in columns.

Lets say for example, I have twelve columns of data for January to December.
I have a separate column which should be summing range January to August, however one of the columns range is January to June and not August.
What will be the easiest way to identify this inconsistency when you have thousands of rows of data?
I used the show formula in the illustration below, however this will not be practical for thousand of row.

Thanks in advance.
I'm using Excel M365

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
1	February	March	April	May	June	July	August	September	October	November	December		Month to Date total
2	88	53	13	36	36	64	69	35	37	36	48		=SUM(A2:H2)
3	7	68	79	28	47	44	98	2	81	47	71		=SUM(A3:H3)
4	35	75	46	55	69	53	60	53	56	50	31		=SUM(A4:H4)
5	33	2	14	67	76	44	37	61	44	54	24		=SUM(A5:H5)
6	60	88	17	31	90	27	71	77	31	74	100		=SUM(A6:H6)
7	50	87	97	26	24	44	23	28	11	11	53		=SUM(A7:H7)
8	47	24	58	65	25	37	30	55	11	25	4		=SUM(A8:F8)
9	4	99	56	51	68	62	88	92	49	97	12		=SUM(A9:H9)
10	36	100	0	10	82	74	11	21	23	75	93		=SUM(A10:H10)
11	84	21	95	98	21	5	94	38	36	40	38		=SUM(A11:H11)
12	13	4	17	77	93	13	77	2	37	100	89		=SUM(A12:H12)
13	26	48	94	86	85	45	14	24	33	62	78		=SUM(A13:F13)
14	34	55	86	22	84	47	14	28	52	68	11		=SUM(A14:H14)
15	16	25	57	62	63	16	82	12	95	48	80		=SUM(A15:H15)
16	88	13	77	45	80	51	49	37	32	48	56		=SUM(A16:H16)
Sheet1

Cell Formulas
Range	Formula
N2:N7,N9:N12,N14:N16	N2	=SUM(A2:H2)
N8,N13	N8	=SUM(A8:F8)

Or you can change reference style in Excel to R1C1: File menu -> Options -> Formulas -> tick checkbox before "R1C1 reference style":

Then in Excel: Formulas menu -> Show formulas: you'll see same formula everywhere except incorrect ones in that column:

Of course you can use VBA or formula (like Exact) to find mismatches.

Regards,

KeepTrying