Formula help please

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3065603%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EFormula%20help%20please%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3065603%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3E%3CSPAN%3EHi%2C%26lt%3B%5C%2FSPAN%26gt%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20have%20the%20following%20data%20and%20currently%20using%2C%20%3Dexact%20to%20check%20columns%20E%20%26amp%3B%20F%20match%2C%20but%20column%20f%20is%20a%20sum%20of%20J%2CK%20%26amp%3B%20L%2C%20I%20would%20like%20a%20formula%20that%20can%20do%20all%20this%20without%20having%20the%20additional%20column%20F%20adding%20everything%20in%20J%2CK%20%26amp%3B%20L%2C%20and%20also%20can%20an%20IFERORR%20be%20used%20for%20blank%20columns%2C%20so%20that%20when%20data%20is%20inputted%20in%20the%20columns%20that%20in%20the%20%22Matched%22%20column%20it%20populates%20the%20TRUE%2FFALSE%3F%26lt%3B%5C%2FSPAN%26gt%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%26nbsp%3B%26lt%3B%5C%2FDIV%26gt%3B%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F340613i2D8CC4C7CA72D7AA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22KellieMeehan_1-1642588910478.png%22%20alt%3D%22%5C%26quot%3BKellieMeehan_1-1642588910478.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3065603%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

Hi,

I have the following data and currently using, =exact to check columns E & F match, but column f is a sum of J,K & L, I would like a formula that can do all this without 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?

 
KellieMeehan_1-1642588910478.png

 

 

6 Replies

@KellieMeehan 

Could you please bit more details what are other formulas for calculations?

@KellieMeehan 

You dont need the Exact() formula. But as you posted this formula you can do something like here

=IFERROR(EXACT(E2,SUM(J2:L2)),"Something")

 

@Juliano-Petrukio 

 

KellieMeehan_0-1642589925053.png

I have added the sum to column E, I would really like to get rid of this and for a formula to look at I2,J2,K2 if matched with D2, for this to say MATCHED in Column F and if it doesn't then UNMATCHED. is this possible? and then instead of having to pull the formula down in ColumnF for eadh row, that like an IFERORR formula that say F6 below stays blank until data is entered, is this possible?

I would like the formula to check that the data in I2,J2,K2 matches D2, and if it doesn't match that in F2 it shows either MATCHED or UNMATCHED, and then in that formula if possible an iferror is no data is in columns I2,J2,K2, hope this makes sense

@KellieMeehan 

Perhaps

=IF( SUM(I2:K2) = 0, "no data", IF( SUM(I2:K2) = D2, "MATCHED", UNMATCHED" ) )

@KellieMeehan 

I try to answer your question

Option1

=IF(AND(ISBLANK(I2),ISBLANK(J2),ISBLANK(K2)),"",IF(OR(ISBLANK(I2),ISBLANK(J2),ISBLANK(K2)),"HASBLANK",IF(D2=SUM(I2:K2),"MATCHED","UNMATCHED")))

Option2

=IF(AND(ISBLANK(I2),ISBLANK(J2),ISBLANK(K2)),"",IF(D2=SUM(I2:K2),"MATCHED","UNMATCHED"))