Counting values from random cells

[clramjit]

Hi,

I'm still new to excel formulas, so please excuse me. I'll get straight to the problem. I have a sample sheet I am working on, attached. I would like to tally the occurrence of both specific Text and Number ("OT" and "1"), occurring in a series of random column cells (not in a straight range), without counting other characters, which have been left out for simplicity. I've tried Count, CountA, CountIF but to no avail. Can you help please. The explanation of what I'd like to achieve is in the callouts. Please let me know if I need to supply more info.

Thanking you in advance for any solution.

Rgds

CLRamjit

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[bosco_yip]

Another option,

1] In E19, copied across right to I19

=SUMPRODUCT(((E$5:E$16="OT")+(E$5:E$16=1))*($B$5:$B$16="X"))

2] In E20, copied across right to I20

=SUMPRODUCT(((E$5:E$16="OT")+(E$5:E$16=1))*($C$5:$C$16="X"))

Regards
Bosco

 

[John Jairo V]

Hi to all!

Another option could be:
[E19:I19] : =SUMPRODUCT((E5:E16={"OT",1})*($B5:$B16="X"))
[E20:I20] : =SUMPRODUCT((E5:E16={"OT",1})*($C5:$C16="X"))

Blessings!

 

[clramjit]

Hi all,

Thanks for all your help. I haven't tried all your suggestions yet, but I am sure it would work for the info I have given. I must apologize, I did indicate that this was a sample sheet I am working on but I did not state that columns B and C were actually Date fields which holds the expiry dates of the training. Would this make any difference in the formulas you supplied? How should this be captured to give the same result?

Thanks again
CLR