Countifs(matchif) forumla? Finding criteria on two sheets, then comparing, then count.

Hello,

I've attached a simplified/desensitized version of what I'm working with.

Two data source sheets, and the report sheet I am building.

Sheet 1 ("Agreed Access") is the access an agent should have.
Sheet 2 ("Current Access") is the access an agent has currently.
Sheet 3 ("Report") is where I'm trying to build the report.

I used Deduplicate of data to create my list of agents and emails on the Report sheet.
I'm trying to figure out how to produce the two following outputs (listed on the Report sheet with notes, and manually filled in correct outputs for examples)

1. Get the Report sheet to grab the email address of the agent in that row, look in Agreed Access, find an access type, then look at Current Access to see if both that email address and that access type exist there. If yes, then count it, putting the count in Column C.

2. Inversely, look at the Current Access sheet, find an email address, what access is there, and if it does NOT match what is on Agreed access, count it, putting the count in Column D.

I've tried Countifs and countifs with vlookups, so far my attempts have been so poor that Excel gives me the 'bad formula/arguments' error every time.

Thank you for the quick reply, Bernie. I'm going to go and apply this now, I will reply back later on today with the results. I would have never thought of buckling up arguments like that!!

Hitting some odd behavior. I type it in exactly as it appears in the formula bar in my actual worksheet:

{=SUM(ISNUMBER(MATCH(_xlfn._xlws.FILTER('Agreed Access'!C:C,'Agreed Access'!B:B=B2),_xlfn._xlws.FILTER('Current Access'!B:B,'Current Access'!A:A=B2),FALSE))*1)}

I press enter, and Excel doesn't recognize it as a formula. Figuring I did something wrong, I went to copy it out of the example workbook to paste it, and it transformed it to:

=SUM(ISNUMBER(MATCH(_xlfn._xlws.FILTER('Agreed Access'!C:C,'Agreed Access'!B:B=B2),_xlfn._xlws.FILTER('Current Access'!B:B,'Current Access'!A:A=B2),FALSE))*1)
This also breaks the formula in the example workbook, changing the output to a zero (whereas before it was properly showing a four)

When I put that in, Excel throws a formula error and asks me if I want to accept it's correction, if I say yes, it transforms the formula to:
=sum(isnumber(match(filter('Agreed Access'!C:C,'Agreed Access'!B:B=B2), filter('Current Access'!B:B,'Current Access'!A:A=B2,FALSE))*1)
This Excel-updated formula returns a Zero for all rows.

What am I doing wrong that it doesn't see your original work, capped with the { }, as a valid formula, and how do I stop Excel from trying to "fix" it when I click into the cell to copy the formula?

Thank you!

Great question, two part answer:
On this machine (my personal computer), it is "Office Professional Plus 2016"

On the company computer it is "Microsoft Apps 365 for Enterprise"

Things are pretty well locked down on that machine, so I had copied your formula out from my personal, printed it, then typed it in manually in the work machine.
How should the formula look on 365?

Thanks Bernie, I apologize for the trouble and confusion. In my response a few posts ago about how Excel was fixing it, it did fix it to what you posted here. When I was typing it back up here, I left out an ) by accident.

I think there is an unknown issue with my workbook. I've tried to rule out user error.

It's returning all zeros for the count if there's a match. I had the printed out sheet up against my first monitor and typed it out, even used the mouse to select sheets and columns to ensure no typos.

I put in the "count if not match" formula, and it returned results, however it's counting every instance that the user appears, not just mismatches. I did another column with a basic "countif" to confirm this was the case. If Smith had a total of 150 rights by the countif (also confirmed with filtering the column), it counted 150 using the iserror formula.

I checked all the columns being called on both sheets to make sure the format was "General" and it is. I did notice that one of the sheets is setup as a Table. Would that cause this behavior?

Edit: I also made sure there were no extra spaces in the data cells that were being called by the formula.

I am going to make your solution as good, because Excel is obviously trying to get them to work, there's just something else I'm missing. If you have any other ideas on things for me to check for one last shot, I would appreciate it. After that, I'll call it quits and just have to make a series of additional calcs in columns on the end of the data sheets and stitch things together from there. I don't want to eat up more of your time with spaghetti-at-the-wall testing since I can't mask and upload a more complete/accurate sample of data.

Thanks Bernie! I will run through all of the above formulas/checks either later tonight or first thing in the morning and report back.
To confirm I didn't have any errant spaces in the data, I manually checked on some like-for-like fields (agent and right) in both sheets.

Hi Bernie, here is the update:

=MATCH(B2,'Agreed Access'!B:B, False)
Strange results! It returned WAY more than it should have. For example, the first email in the list only shows up 247 times in the agreed access sheet, but with this formula it came back as 12,534. All of the other email addresses returned inexplicably high (and inaccurate) numbers. Total rows in the agreed access sheet is about 52K

=COUNTIF('Agreed Access'!B:B,B2)
This returned accurate results. The same person I spot checked above did return a value of 247. I checked a few others, they correctly aligned with this formula output. I have no idea why the first formula went so wonky.


Additional approach you suggested:

=UNIQUE('Agreed Access'!B:B)
Seemed to work, no errors. I have not manually validated this one yet.

=UNIQUE('Agreed Access'!C:C)
This did work, returned a unique list which I did validate matches.

And then use

=COUNTIF('Current Access'!A:A, CellWithFirstFormula)

=COUNTIF('Current Access'!B:B, CellWithSecondFormula)

Both of these seemed to return sane values. I will manually validate a few of them.

So I can speak to it properly, let me make sure I understand the Unique count coupled with the Countif. It's first creating the unique list of agents, then a unique list of rights based off of the agreed access.

The first countif are comparing the unique list of emails on the agreed access against the email of current access, and if it finds a match, it kicks out a number.
The second countif is doing the same with the rights.

So at this point, I can see the output of
"unique Email from agreed access"
"unique Right on agreed access"
"# of times unique email appears on current access"
"# of times unique right appears on current access"

So now, I could do two tables to get a comparison of the number of rights an agent is supposed to have, versus the number of rights they do have. Which is awesome. If I'm misunderstanding that, please pull my coat.

Is there a way to use this new set to get an idea of how many rights they have that they aren't supposed to have (and inherently be able to do the 'double-click-on-value' to see the underlying data of that value pop up in a new sheet as normal)?

Thank you for the explanation. I will copy your formulas into an email from my personal account and send it over to my work account to get them on the computer. I don't know why I didn't think of that first...

I will report back later on today!

Hi Bernie,

Well, I'm happy to report two things:
1) Your formula did work (insert asterisk that we'll hit in a moment)
2) I didn't have any typos -- which I am thrilled about because I would have been absolutely mortified if that was the case.

After emailing formulas, pasted them into notepad so I had references, and pasted the formula from my workbook below it (to both save and compare), the initial results didn't change. So I took your next bit of advice. I fired up a new excel workbook, created my three sheets, named them the same, but only copied in a chunk of each column I actually needed (even putting them in the same column position, using Special Paste of 'values only'). I pasted in your code.... and it worked!

So I knew something has to be wonky with the datasource sheets. I first changed one of them from a Table back to a Range, then did a select all, formatted all the cells as General, then did a Copy, and Special-Paste as "values only". Well... this spun my fairly new work laptop up to full throttle and it stayed there. Eventually Excel Stopped Responding and I had to force close it. So something is definitely wonky someplace in that sheet.

I set a meeting with the team that generated the original source sheets I'm using, and going to ask them to not do anything crazy. In the meantime, I will be copying over the sheets, using the special paste, into a new workbook, and see if that shakes everything loose.

Your solution was apparently spot on this whole time. Once I've had a chance to do this recreation of the data source sheets and put everything back together, I'll report back. Thanks again for all your help! This site has been awesome (I know I don't post much, but I do look for things here and often find answers I need).