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

 

[SidharthS]

u mean u have two criteria??? if so use Countifs instead of countif

 

[Schturman]

u mean u have two criteria??? if so use Countifs instead of countif

It's not two criteria's, only one criteria = name, but I need to find a way how to detect a range of every Friday in the month and after this I can use this range in a COUNT formula.

 

[Leo Skywalker]

Could you upload a sample excel file?

 

[Schturman]

Could you upload a sample excel file?

Sorry, I'm not allowed to upload files here (only pictures) and something named mini-sheet (didn't tried it yet).
You can see the formula that I use now to count the name "Nati" in the Friday ranges (purple colored range):

=COUNTIF($B$23:$J$26,L$3)+COUNTIF($B$51:$J$54,L$3)+COUNTIF($B$79:$J$82,L$3)+COUNTIF($B$107:$J$110,L$3)

Like you see I use here 4 ranges, all of them is Friday in this month and it working perfectly.
My question was if somehow I can detect the ranges of Fridays automatically and change my formula that will count a name from every Friday in a month.
Because every year the range of Friday changed and I need to edit my formula manually....
Thanks

 

[Leo Skywalker]

Sorry, I'm not allowed to upload files here (only pictures) and something named mini-sheet (didn't tried it yet).
You can see the formula that I use now to count the name "Nati" in the Friday ranges (purple colored range):

=COUNTIF($B$23:$J$26,L$3)+COUNTIF($B$51:$J$54,L$3)+COUNTIF($B$79:$J$82,L$3)+COUNTIF($B$107:$J$110,L$3)

Like you see I use here 4 ranges, all of them is Friday in this month and it working perfectly.
My question was if somehow I can detect the ranges of Fridays automatically and change my formula that will count a name from every Friday in a month.
Because every year the range of Friday changed and I need to edit my formula manually....
Thanks
View attachment 76383

The reason I asked for a sample file, is because I see column A filled with what seems to be dates in the format DD/MM but with some text which I can't read next to it, also, I wanted to know where do you get the year from, since it doesn't appear anywhere in this picture, and when do you change the year, is it in another Sheet?, or is it all the way down in the same workbook, or it's maybe that all the info repeats in other columns in the same Sheet. Maybe you can translate the text being shown, so that we can better help.

 

[Schturman]

The reason I asked for a sample file, is because I see column A filled with what seems to be dates in the format DD/MM but with some text which I can't read next to it, also, I wanted to know where do you get the year from, since it doesn't appear anywhere in this picture, and when do you change the year, is it in another Sheet?, or is it all the way down in the same workbook, or it's maybe that all the info repeats in other columns in the same Sheet. Maybe you can translate the text being shown, so that we can better help.

Ok, sorry, I not explained this stuff...
1. In the A3 the date written like: 01/01/2023 and used custom format to show me the specific day and date (it's Hebrew): dddd dd/mm
That mean A3 show me: Sunday 01/01, A7 show me: Monday 02/01 etc...

2. For the next year I use the copy of the same file, just clean the inputs and change the year from 2022 to 2023 by Replace option for whole of Workbook. Range of Fridays changed too and I need edit my formula and change the ranges for Fridays on every sheet (1 sheet = 1 month).
Hope now I explained better

 

[Leo Skywalker]

Wha

Ok, sorry, I not explained this stuff...
1. In the A3 the date written like: 01/01/2023 and used custom format to show me the specific day and date (it's Hebrew): dddd dd/mm
That mean A3 show me: Sunday 01/01, A7 show me: Monday 02/01 etc...

2. For the next year I use the copy of the same file, just clean the inputs and change the year from 2022 to 2023 by Replace option for whole of Workbook. Range of Fridays changed too and I need edit my formula and change the ranges for Fridays on every sheet (1 sheet = 1 month).
Hope now I explained better

What's in columns K, also L and M?, there are a bunch of zeroes.
Can an empty column at the end be used as a helper column?, let's say for example, column P?

 

[Leo Skywalker]

Since cells A3, A4, A5, and A6 are merged, one more helper column will be used to detect if that row is a friday.

Then formulas will be:


in P3:

=IF(A3="",P2,IF(WEEKDAY(A3,1)=6,1,0))

Copy down as needed.

in Q1:

"Nati"

in Q3:

=COUNTIF(B3:O3,$Q$1)

Copy down as needed.


Then, the formula to get the total number of cells in friday rows with "Nati" is:
(supposing the last row in the sum is row 120)

=SUMPRODUCT(P3:P120,Q3:Q120)

 

[Schturman]

Ok, I uploaded my file to dropbox.
In a K column different criteria's for the same name, don't pay attention for this...
L3 and M3 is combined for name, in our case Nati. Like you can see in my formula L$3

=COUNTIF($B$23:$J$26,L$3)

N3 and O3 is combined for next name.
Each name have 2 columns (one for quantities and another one for hours)
Formula that I need for count name Nati (L3) in all Fridays should be in the cell L33 (L33 and M33 combined) and I need drag it left for another names... I named it "TOTAL in Friday:" (Blue color)
See my file, I changed names to English.