count number of times criteria is met

[paulsolar]

Hi All

I've been on this problem for a while and am unable to come up with an answer for myself.

i have two columns both of which contain text columns C & D

what I'm attempting to do count the number of times that the criteria i set are in the same row.

for example if in column C the criteria is Joe Smith and in column D the criteria is Jan-2019 (this is text not a date) then i need to count the number of times these are in the same row.

I don't mind a vba of formulaic solution. I have run out of ideas now and i think it may be something simple but i just cant see it

Thanks in advance

cheers

Paul

 

[Joe4]

You should be able to use COUNTIFS or SUMPRODUCT.

=COUNTIFS(C1:C100,"Joe Smith",D1:D100,"Jan-2019")

See here for more details: https://www.techonthenet.com/excel/formulas/countifs.php

 

[paulsolar]

thanks Joe, you are a star

I cant believe it, I tried what you suggested earlier but got the ranges wrong. As soon as I saw your reply the penny dropped

final formula =COUNTIFS($C$2:$C$7889,K2,$D$2:$D$7889,$L$1) which is in copied down from L2

column K has 2000+ rows as well

Many thanks for your assistance

cheers

Paul

 

[Joe4]

You are welcome.