Trouble with multiple Countifs, multiple criterias (possibly in VBA?)

[zax.cz]

Hi guys,

I have a problem with my current work.
My aim is to shorten and improve current formula:

Code here:

=COUNTIF($E3:$AI3;Legenda!$B$4)*Legenda!F$4 + COUNTIF($E3:$AI3;Legenda!$B$5)*Legenda!F$5 + 
COUNTIF($E3:$AI3;Legenda!$B$6)*Legenda!F$6 + COUNTIF($E3:$AI3;Legenda!$B$7)*Legenda!F$7 + 
COUNTIF($E3:$AI3;Legenda!$B$8)*Legenda!F$8 + COUNTIF($E3:$AI3;Legenda!$B$9)*Legenda!F$9 + 
COUNTIF($E3:$AI3;Legenda!$B$10)*Legenda!F$10 + COUNTIF($E3:$AI3;Legenda!$B$11)*Legenda!F$11 + 
COUNTIF($E3:$AI3;Legenda!$B$12)*Legenda!F$12 + COUNTIF($E3:$AI3;Legenda!$B$13)*Legenda!F$13 + 
COUNTIF($E4:$AI4;Legenda!$K$4)*Legenda!O$4 + COUNTIF($E4:$AI4;Legenda!$K$5)*Legenda!O$5 + 
COUNTIF($E4:$AI4;Legenda!$K$6)*Legenda!O$6 + COUNTIF($E4:$AI4;Legenda!$K$7)*Legenda!O$7 + 
COUNTIF($E4:$AI4;Legenda!$K$8)*Legenda!O$8 + COUNTIF($E4:$AI4;Legenda!$K$9)*Legenda!O$9 + 
COUNTIF($E4:$AI4;Legenda!$K$10)*Legenda!O$10 + COUNTIF($E4:$AI4;Legenda!$K$11)*Legenda!O$11 + 
COUNTIF($E4:$AI4;Legenda!$K$12)*Legenda!O$12 + COUNTIF($E4:$AI4;Legenda!$K$13)*Legenda!O$13 + C3 - D3

Image here:


The whole idea is to make fully automated excel file for attendance. And as there are many departments with different shifts, I wanted to make it as variable as possible.
Currently in column A there is supposed to be name, columns B and C are for last months extra time, then there are 31 columns for days in a month and after that, in column AJ comes my formula.

Current formula works well, however is quite big and that is (except for other stuff) I would like to shorten it. Thought of SUMPRODUCT but it is not very effective for me, so I thought hey, maybe I can use VBA.
I made this little function, which does not work yet :D

Public Function Total(rng As Range) As Long
    
    Dim legenda
    legenda = 4
    Dim colB
    colB = 2
   ' Dim colC
   ' colC = 3
   ' Dim colD
   ' colD = 4
    Dim colE
    colE = 6
    
    Do
        colResult = colD - colC
        Total = Total + CountVIf(rng, Cells(legenda, colB)) * Cells(legenda, colE).Value
        legenda = legenda + 1
        
    Loop Until legenda = 13
End Function

while CountVIf function I got somewhere else on the internet and looks like this

Public Function CountVIf(rng As Range, criteria As String)
    Dim cell As Range, cmd As String
    For Each cell In rng
        If cell.RowHeight <> 0 And cell.ColumnWidth <> 0 Then
        cmd = "COUNTIF(" & cell.Address & ",""" & criteria & """)"
        CountVIf = CountVIf + Evaluate(cmd)
    End If
    Next cell
End Function

I am kind of a newbie in VBA so there is probably something terribly wrong.

"legenda" is a variable, named to know there is a list named like that where there are shifts included, in column B there is a criteria (symbols like D for day shift, N for night etc.) and in column F there are hours worked for that shift (as numbers).

One thing that is not okay for sure is that there is no list (or sheet or whatever it is called in english version) named "Legenda" specified, as I don't know how..

Currently, Function total throws out an error (don't know where's the problem really) and I thought you guys might help

Also, my further plan is to get weekend counted (might be difficult as night shifts from friday goes into weekend) which might need work start and end included - prepared on Legenda sheet.


So what I am asking you is either 1) a way to simplify this formula so it is not 9 lines long or better 2) give me a clue about what I am doing wrong in VBA script and what should be there instead.


Lastly, if anyone else wants, I am willing to distribute this material when it's done so if any of you are insterested, let me know.

 

[barry houdini]

Hello zax.cz, welcome to MrExcel

You could simplify that formula using SUMPRODUCT, i.e.

=SUMPRODUCT(COUNTIF($E3:$AI3;legenda!$B$4:$B$13);legenda!F$4:F$13)+SUMPRODUCT(COUNTIF($E4:$AI4;legenda!$K$4:$K$13); legenda!O$4:O$13)+C3-D3

 

[zax.cz]

Hello zax.cz, welcome to MrExcel

You could simplify that formula using SUMPRODUCT, i.e.

=SUMPRODUCT(COUNTIF($E3:$AI3;legenda!$B$4:$B$13);legenda!F$4:F$13)+SUMPRODUCT(COUNTIF($E4:$AI4;legenda!$K$4:$K$13); legenda!O$4:O$13)+C3-D3

Wow, that is perfect, thank you! Looks much better.


Next comes the weekends problem. As mentioned above, I need the sheet to automaticly count how many hours the person spent at work during a weekend, which is exactly 00:00 on Saturday till 00:00 on Monday.

I am able to autodetect weekend (quite easily as I have a year and month name on the current sheet together with day no. above each column), but how would the formula for counting the time look?

I have no idea how to start (ie. for Friday when, as viewed on print screen from the Legenda sheet, there is a shift since 20:00 till 8:00 giving Friday shifter 8 hours extra pay for weeknds)

classicaly like this I guess.. (no real idea here)

IF(WEEKDAY(DATE($B$1;$C$1;E$2);2)=5; something to happen, dunno what,

Print screen from Legenda sheet (left table for the upper row, right for the lower one)


Thanks for you help everyone!

 

[zax.cz]

A little update..

thought about something like this:

=SUMPRODUCT(
COUNTIF(
   COUNTIF(
      E3:AI3;WEEKDAY(
           DATE($B$1;$C$1;E$2);2)<5);Legenda!B4:B13)
 * SUMPRODUCT(Legenda!D4:D13 - Legenda!C4:C13))

But it does not accept this formula - anybody gets what's wrong? Thanks.



Also I am having slight trouble with "coloring background of easter monday" as a conditioned formating (again, have translated version into czech, the function has different name). It worked nicely for weekends with condition in formula like this

=WEEKDAY(DATE($B$1;$C$1;E$2);2)>5

But it does not for easter like this

=DATE($B$1;$C$1;E$2)=EasterDates!C3

Any ideas?