Cross-Sheet Formula with both columns from "Other" sheet

Melanie Sanders

Hi, I have not been successful in combining 2 formulas together. I have attached a PDF with screenshots and comments. Can someone help?

formula help.pdf

Leibel S

@Melanie Sanders

Seems odd. Try using @cell references.

=COUNTIFS({Location # and Name},@cell=[LOCATION]@row,{Claim Status},@cell="Complete - Credit Received")

Jeff Reisman

Can you share a sample of the {Location # and Name} data and the {Claim Status} data, and tell us the column types the data is in?

Melanie Sanders

Thank you both!

@Leibel Shuchat I entered it in exactly but received error of #INCORRECT. Both of the columns @Jeff Reisman are actually drop-downs which explains the issue, probably. Any workarounds?

Jeff Reisman

Being drop downs shouldn't be an issue unless they are multi-select drop downs. Maybe seeing a data sample of what you're trying to count would help.

Melanie Sanders

@Jeff Reisman They are single select drop-downs.

Here is the cross-sheet.

Jeff Reisman

So I set up dropdowns for Location # and Name and Claim Status in my source sheet.

In my metrics sheet, I made a list of the Location # and Names and created the formula to count the ones that matched the Location for the row with Claim Status "Complete - Credit Received", and it worked.

So I suspect it may have something to do with how you set up your range. In your formula, click on the {Location # and Name} range and choose Edit Reference to see if you're referencing the entire Location # and Name column from your source sheet. It should look like this:

Melanie Sanders

@Jeff Reisman unfortunately that is all correct. Thanks for your time. I am going to step away for a bit and come back with a fresh mindset.

Jeff Reisman

@Melanie Sanders

I'm sorry it's not working. That's very strange.

The causes of #INCORRECT ARGUMENT SET I have listed below. Other things to check:

Sometimes you just have to save the sheet and close and reopen the browser.

Make sure the browser you are using is fully up to date. Your best bets are Chrome, Edge (the newer Chromium-based one,) and Firefox, in that order.

Sometimes you just have recreate the range references in your formulas from scratch.

Both ranges you are counting are in the same source sheet, right? And each Location row has a corresponding Claim Status in the same row?

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

1. For functions that take two ranges: The range sizes don’t match for the function.
2. The function is missing an argument.
3. There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.