Share via

Trying to write a formula that will compare lists on two tabs and count how many are missing from one of the lists based on multiple criteria

Anonymous
2025-01-29T19:51:30+00:00

Example:

Tab 1 data

Variance Batch
Yes ABC123
ABC124
Yes ABC125

Tab 2 data

Variance Batch
ABC123
ABC124
Yes ABC125
Yes ABC126

New: I would like a formula to show that 1 was added to Tab 2 compared to Tab 1

Corrected: I would like a formula to show that 1 was removed from Tab 2 compared to Tab 2

It feels like I need a COUNTIF and XLOOKUP nested, but I can't figure out the right way to set it up

Thanks for any assistance!

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-01-30T19:16:16+00:00

    Fair enough and thank you! I will do my best here:

    Sheet 3 = "Old" and has data as below. This list can be up to about 5,000 rows of data

    A B C D E F G H
    Variance Header Header Header Header Header Header Batch
    Variance Other data Other data Other data Other data Other data Other data ABC5226
    Variance Other data Other data Other data Other data Other data Other data ABC5227
    Variance Other data Other data Other data Other data Other data Other data ABC5228
    Variance Other data Other data Other data Other data Other data Other data ABCM216
    #N/A Other data Other data Other data Other data Other data Other data ABCA212
    #N/A Other data Other data Other data Other data Other data Other data ABCA213
    #N/A Other data Other data Other data Other data Other data Other data ABCA214

    Sheet 2 = "Current" and has data as below

    A B C D E F G H
    Variance Header Header Header Header Header Header Batch
    #N/A Other data Other data Other data Other data Other data Other data ABC5226
    Variance Other data Other data Other data Other data Other data Other data ABC5227
    Variance Other data Other data Other data Other data Other data Other data ABC5228
    Variance Other data Other data Other data Other data Other data Other data ABCM216
    #N/A Other data Other data Other data Other data Other data Other data ABCA212
    #N/A Other data Other data Other data Other data Other data Other data ABCA213
    #N/A Other data Other data Other data Other data Other data Other data ABCA214
    Variance Other data Other data Other data Other data Other data Other data ABCA215

    Sheet 1 = "Dashboard" and has data below with columns A-D hidden

    A B C D E F
    Current Old Completed =COUNTIF(D:D,"1")
    Variance Variance New =COUNTIF(B:B,"1")
    =FILTER(Current!H:H,Current!A:A=A2,"") =IF(A4<>"",XLOOKUP(A4,C:C,C:C,"1"),"") =FILTER(Old!H:H,Old!A:A=C2,"") =IF(C4<>"",XLOOKUP(C4,A:A,A:A,"1"),"") Total Outstanding =COUNTA(A4:A2000)

    So in this example data set:

    Completed would be a count of any batches which had Variance in the Old tab and #N/A in the Current Tab. It counts ABC5226 and returns a result of 1

    New would be a count of any batches which have Variance in the Current Tab and either did not exist, or had #N/A in the Old Tab. It counts ABCA215 and returns a result of 1

    Total Outstanding is just a count of Variance in the Current Tab and returns a result of 4

    What I have in "Dashboard" is getting me the result I need, but I'm trying to work it into a single formula.

    Hope this helps clarify!

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-01-30T14:41:16+00:00

    Your original post did not include any mention of the values of column A in any way. If you post a small example, what you would expect from that example, and an explanation of your logic, then we can come up with a single formula solution.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-01-30T00:52:52+00:00

    There are some fancy things nested in there!

    These does not seem to take into account Column A however.

    For the "Corrected" formula, I'm trying to show that batch ABC123 no longer has a variance, even though it is still listed in column B

    Separately, for the "New" formula, I want to show that Batch ABC126 now has a variance, even though it wasn't in Sheet1 at all

    I was able to make something work by using helper columns, but was trying to avoid those if I could build it all into one formula.

    The solution that worked was using four additional columns in Sheet3:

    A B C D
    =FILTER(Sheet1!B:B,Sheet1!A:A="Variance","") =IF(A4<>"",XLOOKUP(A4,C:C,C:C,"1"),"") =FILTER(Sheet2!B:B,Sheet2!A:A="Variance","") =IF(C4<>"",XLOOKUP(C4,A:A,A:A,"1"),"")
    --- --- --- ---

    I hid those columns and then added these

    Corrected: =COUNTIF(D:D,"1")
    New: =COUNTIF(B:B,"1")

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2025-01-29T23:13:29+00:00

    Hi,

    In cell D9, i entered this formula

    =COUNTA(E2:E5)-SUM(COUNTIF(E2:E5,B2:B4))

    Hope this helps.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2025-01-29T22:21:27+00:00

    I have assumed your tabs are named Sheet1 and Sheet2, with values in column B of both - this will count items on Sheet2 that do not appear on Sheet1:

    =LET(d,FILTER(Sheet2!B:B,Sheet2!B:B<>""),SUM(BYROW(d,LAMBDA(x,IF(ISERROR(MATCH(x,Sheet1!B:B,FALSE)),1,0)))))

    IF you have duplicates in either your list, then... it depends on what you actually want. You could try

    =LET(d,UNIQUE(FILTER(Sheet2!B:B,Sheet2!B:B<>"")),SUM(BYROW(d,LAMBDA(x,IF(ISERROR(MATCH(x,Sheet1!B:B,FALSE)),1,0)))))

    Was this answer helpful?

    0 comments