Using IFERROR, IF and COUNTIF in one time

This should work.

=IFERROR(Table 3::$A$2×IF(OR(1 Dec 23 Situation="Sick", 1 Dec 23 Situation="Holiday"),8,DUR2HOURS(1 Dec 23 Working hours)),"...")

This assumes that you are paid 8 hours for both sick days and vacations, at the same hourly rate.

The problem with you formula is that is is, in my opinion, mis-using countif() inside of another IF. It is confusing and not necessary,

Change the formula in G2 to:

=IFERROR(IF(OR(C2="Sick", C2="Holiday"),8,DUR2HOURS(F2))×XLOOKUP(C2, Rates::A, Rates::B, "Choose a work type",match-type,search-type), "")

then fill down. This will force work hours to 8 on the days you enter "Sick" or "Holiday"

I suggest trying a different method:

1) create a small table where your various work rates go:

I am unclear, based on your example that the "Extra Work" makes sense. if you are really working for 17.243 Euros for regular pay, then dropping to 10 for extra you should NOT take extra work (this is my opinion). So maybe this is supposed to be pay on top of regular pay. In which case the formulas will be a little different.

2) Create a single table where you enter your work:

enter two subsequent dates in cells A2 and A3, then select the two dates. Hover the cursor over the bottom edge of the selection. click and hold on the yellow fill control that appears. Drag down as needed

select cell B2, then type (or copy and paste from here) the formula:

=DAYNAME(1+MOD(ROW()−1, 7))

short hand for this formula is:

B2=DAYNAME(1+MOD(ROW()−1, 7))

I notice you are from the EU. In this case you should modify the formulas I post to use the semicolon as the delimiter... like this:

=DAYNAME(1+MOD(ROW()−1; 7))

to fill down, select cell B2, copy

select cells B2 thru the end of columnB, paste

Create a pop up menu from the table Rate for the type of work. Select a cell with that pop-up menu and use it for the Work Type column (column C)

Looks like you get a 45 minute break in an Regular work shift and that regular hours are limited to 8 hours.

F2=IF(COUNTA(D2:E2)>1, MIN("8h", E2−D2), "")

G2=IFERROR(DUR2HOURS(F2)×XLOOKUP(C2, Rates::A, Rates::B, "Choose a work type"), "")

H2=IFERROR(IF((E2−D2)>DURATION(weeks,days,8,45),(E2−D2)−DURATION(weeks,days,8,45), ""), 0)

I2=IFERROR(DUR2HOURS(H2)×XLOOKUP("Extra Work Rate", Rates::A, Rates::B, 0), 0)

J2=IFERROR(I2+G2, "")

Select F2 then J2, copy

select F2 thru the end of column J, paste

Be careful here:

K8=IF(B8="Sunday",SUM(J2:J8), "")

select cell K8, copy

select cells K8 thru the end of column K, paste

DO NOT FORGET... replace the commas in my formulas with semicolons

In your first post, you had the formula in the column with 1 Dec 23 in the header.

That's why I reproduced exactly your situation to make it easier to just paste the formula at the same location (and then extend it to other columns, which would have adjusted the references).

But you used my formula in a column with a different header, hence the reason why it didn't work. You need to understand the formulas posted as solutions and adjust to your particular situation.

1 Dec 23 Working Hours is at the intersection of column 1 Dec 23 and row Working Hours.

repost your table with more dat entries and expected results. If you do not want to share specifics of your situation, make up some numbers that are representative.

Also... please show the other tables your formula uses as those often help us understand your problem and present a solution for you

Finally, describe what you want in each situation leave out the formulas you think you should use just describe what you want in words

I would like to correct a formula and add a feature I missed:

H2=IFERROR(IF((E2−D2)>DURATION(weeks,days,8,45),(E2−D2)−DURATION(weeks,days,8,45), "0h"), "0h")

fill this down as needed.

You may summarize a single year using the following table:

Create a new table named "Summary" enter row A as shown

A2=ROW()−1&"/1/"&RIGHT(A$1, 4)

I have entered this for US dates... so I will explain the pieces so you can modify if needed:

Month is "ROW()−1"

Day is always 1

Year is obtained using "Right(A$1, 4)" (the 4 rightmost characters of the string in cell A1)

B2=SUMIFS(Table 3::F,Table 3::$A, ">="&DATE(YEAR($A2), MONTH($A2), 1), Table 3::$A, "<="&EOMONTH(DATE(YEAR($A2), MONTH($A2), 1),0))

C2=SUMIFS(Table 3::H,Table 3::$A, ">="&DATE(YEAR($A2), MONTH($A2), 1), Table 3::$A, "<="&EOMONTH(DATE(YEAR($A2), MONTH($A2), 1),0))

select cells A2 thru C2, copy

select cells A2 thru C13, paste

I do not understand your formula and I cannot really figure out what you are trying to do. While the formula is syntactically correct, I cannot understand that it is supposed to do.

Please describe what you are trying to do in words. Let us help with the formula.

did any of the posts in this thread solve your problem?