Countifs with OR or maybe other function?

[jyadayada]

I have a sheet with 1700 rows, Column A contains multiple entries for each "Week 01" through "Week 24", Column L is blank or contains a "1" and Column AN contains "JSG1" through "JSG10", I was starting with countifs but the OR part threw me what I want a count of is:

All the instances where column A = "Week 01" AND Column L = "1" AND Column AN Contains either "JSG5" OR "JSG6"

these were suggested:
=SUMPRODUCT(--(A1:A10="Week 01"),--(L1:L10=1),--((AN1:AN10=5)+(AN1:AN10=6)>0))

or maybe:

=SUMPRODUCT(--(A1:A10="Week 01"),--(L1:L10="1"),--((AN1:AN10="5")+(AN1:AN10="6")>0))

or even:

=SUMPRODUCT(--(A1:A10="Week 01"),--(L1:L10=1),--((AN1:AN10="5")+(AN1:AN10="6")>0))

but I had previously referencedthe AN data as just a numeric for anonymisation are the above 3 options not running because of the alpha prefix in AN? I had changed the ranges to reflect my data........

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.


See next post.

[JohnTopley]

Try


=SUMPRODUCT(--(A1:A10="Week 01"),--(L1:L10="1"),--((AN1:AN10="JSG5")+(AN1:AN10="JSG6")))