CODEWITHSUNDEEP
excelcountif
TYPOI
TYPOI

Reputation: 389

w/ Multiple Criteria Way to check work?

I need an answer to match three criteria. Count if there is a "Y" in the CC column, an "N" in the DD column, and the text in the BB column does not start with an "A"...

My formula is:

=COUNTIFS(groceryStore!CC2:CC1456,"Y",groceryStore!DD2:DD1456,"N",groceryStore!BB2:BB1456, "<>A*")

I have looked through the wealth of very good answers on StackOverflow for Countifs and came up with the above.

The answer I'm getting seems to be plausible. Is my formula correct? The spreadsheet is very large. How can I proof it to be sure my answer is correct?

Upvotes: 0

Views: 31

Answers (2)

user4039065
user4039065

Reputation:

Tap F5 and type or paste BB:DD into the Reference: text box then click OK.

With all cells in the BB:DD range selected, choose Data ► Sort & Filter ► Filter. Set up these filters using the filter dropdowns in row 1.

  • For BB, use Text Filters ► Custom Filter ► Does not begin with ► A
  • For CC uncheck (Select All) then check Y from the available filter choices listed.
  • For DD uncheck (Select All) then check N from the available filter choices listed.

The number of matching rows should be listed at the right-hand side of the status bar. You can hide unimportant columns between BB, CC & DD to visually compare the results.

Excel's COUNTIFS function should be reporting the same results.

Upvotes: 1

Maarten Van Hellemont
Maarten Van Hellemont

Reputation: 35

Do a COUNTIF for every criteria separately and see if the sum of those equals your own COUNTIF ?

otherwise no idea

Upvotes: 0

Related Questions