Vlookup and count function

**Kezwick** · 09-05-2012, 06:17 AM

Hi Guys,(and gals)

Am tearing my hair out the little i have left, im looking for a function that seemed simple at first, i have a table (A:D), in column "A" there is a name and coloumn "D" a 1 or 0, i need a function to do a v lookup on an coloumn and then count all the ones that are assosiated with that name,

my effort was

Please Login or Register  to view this content.

there are a number of other names in "A" and only 1's and 0's in "D" but not all names have a 1 its sort of like

-A------B-----C------D
TSTL----x-----x------1
PRAX----x-----x------1
TSTL----x-----x------0
PAS-----x-----x------0
TSTL----x-----x------1

so this would be 2

i would add a better example but cant appologies

Many thanks in advance will keep trying till im bauld!

**Spencer101** · 09-05-2012, 06:20 AM

If you have Excel 2007, you could do this with COUNTIFS.

=COUNTIFS(A:A,"TSTL",D:D,1)

In earlier versions you can use SUMPRODUCT.

=SUMPRODUCT((A1:A100="TSTL")*(D1:D100=1))

(edit the data ranges in the second version to match your dataset)

**Kezwick** · 09-05-2012, 07:34 AM

you are a legend! i new i was close haha thanks bud can keep my hair ha ha

**Spencer101** · 09-05-2012, 07:40 AM

Glad to help mate

Make sure you mark the thread as solved, or the mods will come knocking...

**Cutter** · 09-05-2012, 04:15 PM

@ Kezwick - Knock, Knock

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.