Fomula to Match Items in a List

[Justinian]

I have a spreadsheet and in column G are codes like IP (in progress) or SS (short-staffed), among others. These codes are a saved range called "Codes" and they are in column AH. AH is a dynamic range so that is why there is no cell reference here. I want to use a COUNTIF formula to count all codes from AH that appear in column G but do not know how to construct the formula with MATCH or INDEX.

 

[FDibbins]

1. Can you show the formula you are using?

2. Also do a quick test for me. Pick a criteria (say, A1), and then find a cell in your data that you know contains that criteria (say Z100)
Now enter =exact(A1,Z100) tell me what it returns

I am thinking that perhaps your count criteria and your data, don't match properly

 

[Justinian]

In cell B4:

=COUNTIF('Week 44'!G:G,Codes)

I did your exact example and it returned a TRUE.

 

[FDibbins]

aahh OK that wont work, you are trying to count all codes in your data range - you need to us an ARRAY version for that. Try this...
=SUM(COUNTIF('Week 44'!G1:G1000,Codes))
entered using CTRL SHIFT ENTER, not just enter

Note that I changed the data range, try to avoid using full-column ranges in array formulas, it will slow your file down

 

[Justinian]

Wow, that worked. I guess I need a better understanding of arrays. How does adding SUM fix that formula and what is difference between arrays and normal formulas?

 

[FDibbins]

from this site (they explain it better than I could)...

https://www.google.com/search?sourc...31k1j0i131i46k1j46i131k1j0i46k1.0.XXnQmDwwGBs

An array formula is a formula that can perform multiple calculations on one or more of the items in an array . ... Array formulas can return either multiple results or a single result. For example, you can create an array formula in a range of cells and use the array formula to calculate a column or row of subtotals.

Soi in this case, the ARRAY returns a series of 1's and NA's, the SUM sums the 1's and ignores the errors

 

[Justinian]

Thank you very much.

 

[FDibbins]

Happy to help