countif contains - multiple range?

[keinz]

is there a possibility to use the countif function with the contains functionality like - =countif(A4:A100,"*a*") but cross references with another column to ensure to only count if column B is >0 ?

What I am trying to do is:

Column A has a list of availability submitted by employees
8AM-12PM
8AM-1PM
830AM-2PM
and so forth

based on their availability and hours - I put a number that counts how many staff are available in column B
0
2
1
2
and so forth

so what I am trying to do is create a formula that allows me to classify how many "openers" i have (ie. people who can work at 8AM) and closers (people who can work until 5PM)

 

[etaf]

what are all the possible entries ?

the trouble is possible converting the text entry as you may have 8am and 8pm for example - so the text needs to be extracted and turned into a number for time possibly

if the entry is free form for all employees - I suspect there maybe a lot of discrepancy

for example , if people added 7am, 730am, 745am 8am - then thats 4 people able to work at 8am - so we need to do a <= 8am

maybe other member may help - but i was concerned about the actual data entry

I dont suppose you could have a start and finsh column
then they could enter as time - with some data validation

07:45 in column A 14:15 in column b

 

[keinz]

i kinda figured it somewhat..

is there a way to do a countifs function within a sumifs function or vice versa?

currently i have it like this:

=COUNTIFS(J7:J104,"*8AM*",K7:K104,">0")

but its only counting K7:K104, when I need it to sum

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try

=SUMIFS(K10:K104,J10:J104,"*8AM*")