Need formula to fill X else 0

[cracker_92]

Good Day All. I am hoping for some help

I have a file that need to be filled with X, searching for the formula how to fill it according to the requirement.

Please find the attachment, For the good understanding i have provided with possible examples including with pictorial one's.

Any help would be greatly appreciated.

Awaiting for the help

[63falcondude]

Hello and welcome to the forum.

Try this in B2:

=IF(COUNTIFS(Data!$A:$A,B$1,Data!$B:$B,$A2),"X","")

Drag to the right and down.

If you want 0 instead of blank, replace "" with 0.

[cracker_92]

Thanks for your quick response and greetings,

Was very happy to see the response at light speed.

Can you please help me to fix this below error:

Attachment 650671

My excel reads comma seperator value as ; instead of :

So i tried even that but still issue persists

[63falcondude]

The attachment didn't work.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[cracker_92]

Please find the attachment of the error, Thanks for your help once again.

[AliGW]

Do you have a European locale on your PC?

Try this:

=IF(COUNTIFS(Data!$A:$A;B$1;Data!$B:$B;$A2);"X";"")

[cracker_92]

Thanks a lot for the formula it really worked.

There is a new column added along with the new case (case-3), Could you please help a formula for this case as well

Please find the attachment

Awaiting for help !

[63falcondude]

Please don't send personal messages. We see when new posts are added to threads that we contribute too.

You can use this in C2:

=IF(COUNTIFS(Data!$A:$A,C$1,Data!$B:$B,$A2,Data!$C:$C,$B2),"X","")

or if your regional settings require ; instead, you can use this:

=IF(COUNTIFS(Data!$A:$A;C$1;Data!$B:$B;$A2;Data!$C:$C;$B2);"X";"")

[cracker_92]

Thank you both for your help
Have understood the forum rules as well..
Wish you a good day ahead.

[63falcondude]

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.