SumProduct /Countif

[Russ76]

Hi,
I am trying to create a formula which gives the total count of how many times the codes in A1:A8 in the 'Reference' tab are found in column A of the 'Check' tab.
Cheers.

[AlKey]

Hi Russ76

Try this formula in B3 and copy it down

=COUNTIF(Check!$A$2:$A$5789,A3)

[Russ76]

Hi, sorry I didn't clarify properly. I want to get a single figure which gives me the combined total of the number of times that these codes appear. So say in A25 of the reference tab is a total cell.

[Glenn Kennedy]

Try this, then - entered as an array formula (CTRL + SHIFT + ENTER to set). BtW, there is an extra space after DEP in A8 that you need to delete...

Please Login or Register  to view this content.

[AlKey]

Here is the total count of the green cell codes

=SUM(COUNTIF(Check!$A$2:$A$5789,$A$3:$A$10))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[Glenn Kennedy]

Russ, exactly what cells do you want to count... to A8, as mentioned in Post 1, or the green ones (A3:a10)? If the latter, my answer would need to nbe modified and it would then look exactly the same as Alkey's...

[Russ76]

Hi. I am counting the codes in A3 to A10 in 'Reference'. I have plugged the above formula in but it isn't counting the number of times 'DEP' appears which is 2527. It is missing these out and only giving 3174.

[AlKey]

this is because you have trailing spaces after the code DEP. Just remove space and formula will pick up.

[Yogi52o]

Can also use SUMPRODUCT if you don't wan't the C.S.E.

Please Login or Register  to view this content.

As mentioned, you can alter the range A3:A10 to be whatever criteria list you would like.

[Russ76]

Thats really bloody annoying...thanks for your help with this.

[Glenn Kennedy]

See second sentence of post# 4...