Formula help

[KellieMeehan]

Hi,

I have the following data and currently using, =exact to check columns E & F, but f is a sum of J,K & L, I would like a formula that can do all this with out having the additional column F adding everything in J,K & L, and also can an IFERORR be used for blank columns, so that when data is inputted in the columns that in the "Matched" column it populates the TRUE/FALSE?

Thank you for reading

 

[AlanY]

how about?

=iferror(and(exact(E2,F2),F2=J2+K2+L2),"")

 

[KellieMeehan]

Sorry I might not have explained myself clearly, I want to remove column f completely, I added this in because it was the only way I could get the formula to work.

I want Column E, to do =exact in "Matched" column G, but adding columns J,K & L, but where there is no data that column G is blank until data is inputted, is this possible?

 

[FormR]

Hi, you don't need the EXACT() function to compare numbers.

I would like a formula that can do all this with out having the additional column F adding everything in J,K & L,

Book2
	E	F	G	H	I	J	K	L	M
1	Total		Matched			attended	dna	unused	backfills
2	14		TRUE			8	4	2	1
3	49		TRUE			26	18	5	6
4	86		TRUE			28	51	7	13
5	58		FALSE			29	26
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=E2=SUM(J2:L2)

and also can an IFERORR be used for blank columns, so that when data is inputted in the columns that in the "Matched" column it populates the TRUE/FALSE?

Which column(s) need to be populated before the calculation is attempted? Just column E?

 

[KellieMeehan]

I want the EXACT function so I can see if the number of attended/dna/unused match the total in column E,

 

[FormR]

I want the EXACT function so I can see if the number of attended/dna/unused match the total in column E

For that, you can use a simple equality test as shown in post #4 - the EXACT() function is designed for case sensitive checking of text strings - not numbers.

 

[KellieMeehan]

I want the EXACT function so I can see if the number of attended/dna/unused mat

 

[KellieMeehan]

The formula above doesn't appear to be working, I want COLUMNF to say true or false if they match or not, and then any additional rows that COLUMNF only populates when data is added instead of dragging it down from the previous row:-

 

[FormR]

I want the EXACT function so I can see if the number of attended/dna/unused mat

Have you simply tried the suggestion in post#4?

i.e.

=E2=SUM(J2:L2)

 

[KellieMeehan]

Have you simply tried the suggestion in post#4?

i.e.

=E2=SUM(J2:L2)

how about?

=iferror(and(exact(E2,F2),F2=J2+K2+L2),"")

Hi, the above formula doesn't appear to work on my spreadsheet