#INVALID OPERATION while using COUNTIFS

Mindfull

Hi,

I am trying to fetch a value based on three columns the columns are as under:

I am trying to filter out how many requests are there in the two types against each "LOB category", I tried Count IF and phrased it as :

=COUNTIFS({DocRequestTypAPAC}, $[Document Request Type]@row, {LOBIRS} = "IRS", {RRmnth}, "October 2020"). it is throwing and #INVALID OPERATION Error.

Please hep to get corrected.

Prajna

Andrée Starå

@Prajna Jain

Try something like this.

=COUNTIFS({DocRequestTypAPAC}, $[Document Request Type]@row, {LOBIRS}, "IRS", {RRmnth}, "October 2020")

Did that fix it?

Andrée Starå

Hi @Prajna Jain

At a glance, could it be a spelling error?

It looks like there is a missing e in the cross-sheet reference.

=COUNTIFS({DocRequestTypeAPAC}, $[Document Request Type]@row, {LOBIRS} = "IRS", {RRmnth}, "October 2020").

Did that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Mindfull

@Andrée Starå,

I cannot see it is because of spelling, as it is the range name that i have defined in short.

Andrée Starå

@Prajna Jain

On second glance I think it's the $ sign because you can't use Absolute Reference with @row.

Was that the issue?

Mindfull

@Andrée Starå,

I have used Absolute Reference with @row in another formula for Count IFS those are working fine. Hence, I don't see $ sign as a cause of the error.

Tough I tried removing $, still throwing same error.😔

Andrée Starå

@Prajna Jain

I almost certain that the Absolute Reference didn't work before with @row, but I tested, and it works now, so that is fantastic. 🤩 

Regarding your issue

I'd be happy to take a quick look.

Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Mindfull

@Andrée Starå 

Yes, and if I am phrasing the COUNTIFS correctly why it is throwing ERROR.

Can it be due to 3 range/criteria are being called ?

I have check COUNTIFS works perfectly with 2 criteria.

Andrée Starå

@Prajna Jain

Try something like this.

=COUNTIFS({DocRequestTypAPAC}, $[Document Request Type]@row, {LOBIRS}, "IRS", {RRmnth}, "October 2020")

Did that fix it?

Mindfull

Yessss I did it,

=COUNTIFS({DocRequestTypAPAC}, $[Document Request Type]@row, {LOBIRS},"IRS", {RRmnth}, "October 2020")

I just removed "=" and put a coma there. 🙂

Andrée Starå

@Prajna Jain

Haha! That's what I just wrote.

Glad you got it working!

Mindfull

@Andrée Starå 

Ohhh..my bad...saw your reply late..😮

Andrée Starå

@Prajna Jain

Haha! No worries!

The important thing is that it's working.

I don't know how I could miss it.

Time for more coffee! 😉