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]

Why would you want to use INDEX/MATCH to count something (I/M will only ever return the 1st match it finds, then stop looking)? If you are only counting based on 1 criteria, COUNTIF should work. If you have more than 1 criteria, use countifS()

 

[Justinian]

Thus the reason for my question as COUNTIF is not working.

 

[FDibbins]

I still don't see the problem here?
Are you counting based on 1 criteria?

 

[Justinian]

Yes.

 

[FDibbins]

OK so what's the problem?
is countif() not working for you?
If not, what is it returning, compared to what you expected?
Show some sample data please?

 

[Eric W]

It would help if you could show us what your input looks like, and what you want for output. The best I could gather from your initial request is something like this:

	G	H	AG	AH	AI	AJ	AK
1	Codes			Codes	Count	Total	Total
2	IP			IP	4	12	12
3	SS			SS	1
4	XY			XY	4
5	IP			AB	2
6	IP			CD	1
7	XY			EF	0
8	AB			GH	0
9	IP			IJ	0
10	AB				0
11	XY
12	CD
13	XY
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula AI2 =COUNTIF(G:G,AH2) AJ2 =SUM(AI:AI) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula AK2 {=SUM(COUNTIF(G:G,AH2:AH20))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Codes in G, possible duplicates. List of possible values in AH. Then the COUNTIF to count specific codes could be placed in AI2, and copied down. Then if you want the total of all values, you could just SUM column AI, which is the formula in AJ2. If you want the sum of the values without using column AI, then the array formula in AK2 will work. If you have the code range (AH2:AH20 in this example) defined as a dynamic range, just use that name in the formula instead.

 

[Justinian]

Here is a link to my workbook:

https://app.box.com/s/kf4ib8ki90hjc8tpuxlfxgjjaf8itjay

I am trying to use COUNTIF on the dashboard tab in B4 but it will not work. The dynamic range of codes is on Week 44 tab in column AH (named "Codes").

 

[FDibbins]

I am trying to use COUNTIF on the dashboard tab in B4 but it will not work.

What does that mean?
Wrong answer?
no answer?
Something else?

 

[Justinian]

It returns a 0.