How to COUNTIF a name in specific range ?

[Schturman]

Hi to all
How I can COUNTIF for a range of cells that related to a specific day? For example the green range is a range related to a Friday and I need COUNT a name "אוקסנה" in this range.
The total range from A3 to J126, formula need to detect every Friday and count a specific name in this ranges (in this case range of Friday is B7:J10) like:

=COUNTIF(RANGE OF EVREY FRIDAY,"אוקסנה")

Thanks

 

[Leo Skywalker]

OK,

That's much better!!

Anyways, as dates in column A are merged, you will need another column to have all the rows to know if they are friday, I am taking column DD.
(the other alternative is to unmerge all the days, and have every cell showing the date)

DD1:

IS THE ROW FRIDAY?

Formula in DD3:

=IF(A3="",DD2,IF(WEEKDAY(A3,1)=6,TRUE,0))

And copy down up to DD130

then in L33:

=IF(L3="",0,SUMPRODUCT(($DD$3:$DD$130)*($B$3:$J$130=L$3)))

Drag as required.

 

[Schturman]

OK,

That's much better!!

Anyways, as dates in column A are merged, you will need another column to have all the rows to know if they are friday, I am taking column DD.
(the other alternative is to unmerge all the days, and have every cell showing the date)

DD1:

IS THE ROW FRIDAY?

Formula in DD3:

=IF(A3="",DD2,IF(WEEKDAY(A3,1)=6,TRUE,0))

And copy down up to DD130

then in L33:

=IF(L3="",0,SUMPRODUCT(($DD$3:$DD$130)*($B$3:$J$130=L$3)))

Drag as required.

Hi.
I'm sorry for the late answer (too much work and no time). Yes, your method working good, Thanks !
But now I decided to use 2 criteria's for count, not only name. That mean Name and empty cell or any number. If it see the word "ביטול" (canceled in Hebrew) near the name, it will not count this name.
In this case I needed to use a manual ranges - 3 times for each Friday. It gave me a very long formula (see pic), but it work very good. I don't know how to do it better...

Thanks for your help!

 

[Leo Skywalker]

Wow,
That's a big, long formula...

Try instead:

=IF(L$3="",0,SUMPRODUCT(($DD$3:$DD$130)*($B3:$H130=L$3)*($C3:$I130<>"CANCELED")))

Replace the word "CANCELED" with the translation.

and let me know if it worked.

 

[Schturman]

Wow,
That's a big, long formula...

Try instead:

=IF(L$3="",0,SUMPRODUCT(($DD$3:$DD$130)*($B3:$H130=L$3)*($C3:$I130<>"CANCELED")))

Replace the word "CANCELED" with the translation.

and let me know if it worked.

It working perfectly, thanks! But I forgot to say it should count also the same name like in L$3 but with symbol "*" or "^".
For example in L$3 = Sarah, but in the ranges it can be Sarah or Sarah* or Sarah^ it should count all of them... When I use COUNTIFS in my formula it work with &"*" for L$3.
I tried to add to your formula &"*" to L$3, but it not worked...

 

[Leo Skywalker]

Will it be OK if it counts all Names starting with the name is L3??, if so, this will work:

=SUMPRODUCT(($DD$3:$DD$130)*(LEFT($B3:$H130,LEN(L$3))=L$3)*($C3:$I130<>"CANCELED"))

So, if L3="Diana" then "Diana", "Diana*", "Diana^", "DianaXX", "Diana 4", "Diana?????" or any other that starts with "Diana" will be counted.

 

[Schturman]

Will it be OK if it counts all Names starting with the name is L3??, if so, this will work:

=SUMPRODUCT(($DD$3:$DD$130)*(LEFT($B3:$H130,LEN(L$3))=L$3)*($C3:$I130<>"CANCELED"))

So, if L3="Diana" then "Diana", "Diana*", "Diana^", "DianaXX", "Diana 4", "Diana?????" or any other that starts with "Diana" will be counted.

Very cool, thank you !!!
Exactly what I needed !

 

[Schturman]

Another question....
Why you separated formulas ?
Can it be somehow used in one-line formula ?

 

[Leo Skywalker]

Another question....
Why you separated formulas ?
Can it be somehow used in one-line formula ?

What formulas are Separated?
There is only one formula:

=SUMPRODUCT(($DD$3:$DD$130)*(LEFT($B3:$H130,LEN(L$3))=L$3)*($C3:$I130<>"CANCELED"))

If you are referring to the formulas in Column DD, theose are because your cells in Column A are merged, and you need to check every row, if you wanted not to have formulas in column DD, then you would have to unmerge the cells in AA and have ever row with its corresponding day.

 

[Schturman]

What formulas are Separated?
There is only one formula:

=SUMPRODUCT(($DD$3:$DD$130)*(LEFT($B3:$H130,LEN(L$3))=L$3)*($C3:$I130<>"CANCELED"))

If you are referring to the formulas in Column DD, theose are because your cells in Column A are merged, and you need to check every row, if you wanted not to have formulas in column DD, then you would have to unmerge the cells in AA and have ever row with its corresponding day.

Ahhh... I understand...
Ok, no problem... Your way still is better than my. Every year I need to update my formula (ranges) because Friday moving and now with your formula I don't need to do this
Thank you for your help !

 

[Schturman]

Hi again.
I have another question...
I have another table that should SUM a hours weekly for a specific name.. Something like we did for Friday, but here I need hours weekly.. That mean it should detect a range for every week in a month.
In my original file I created ranges manually for every week... And I need update it every year...

Now I created another column like we did for Friday:

=IF(OR($A3="",$A3="* חודש הבא *"),AG2,IF(AND(WEEKDAY($A3,1)>=1,WEEKDAY($A3,1)<=6),TRUE,0))

It show me correctly TRUE for each week and zero for Saturday.
What formula I can use to SUM hours weekly (separately for each week) for specific name ?
Thanks