Excel - SUMIF Function

user_14326912 0
Viewed 62

I made a worksheet covering meal expenses for a set period of time (01-08-2020 until 31-08-2020.) The company will pay up to 75kr, Monday-Friday. The list covers expenses 7 days a week, If the amount is greater than 75 on Monday-Friday, I have to pay the extra expenses. (This I have figured out correctly). Saturday and Sunday I have to pay fully.

So my question is, how can I sum this and exclude Saturday and Sunday?

Attempt 1: I tried this formula (Norwegian excel. Summerhvis = SUMIF. Lørdag = Saturday): =SUMMERHVIS(B2:B32,"<>lørdag", G2:G32)

It seems to be partly correct however, I miss Sunday, how can I add Sunday to this equation?

Attempt 2: I used this formula: =SUMMERHVIS(B2:B32,B2:B6,G2:G32)

It gave the correct answer but the answer was layered in 5 rows. I then summed this in P8. Where I want the answer is in G32. So in G32 I wrote =p8.

It must be an easier way of doing this?

enter image description here

enter image description here

sumifs excel
1 Answers

You can use SUMPRODUCT and the WEEKDAY function:

=SUMPRODUCT((WEEKDAY(A2:A32,3)<5)*G2:G32)

According to the Microsoft function translator, this might be, in Norwegian:

=SUMMERPRODUKT((UKEDAG(A2:A32;3)<5)*G2:G32)

If you really want to use SUMIF and your text days, one way would be (in English):

=SUM(SUMIF(B2:B32,{"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"},G2:G32)),

or, if your weekday days are in b4:b8:

=SUM(SUMIF(B2:B32,$B$4:$B$8,G2:G32))

but I'd advise against it as it would only work in the language of the text days of the week. Also, I note your days of the week don't seem to match up with what the days of the week were in the US. For example, here 1 Aug 2020 was a Saturday, so another possible cause for error (unless I am not understanding the dates correctly).

The above is the same as summing five separate SUMIF equations, each for a desired day of the week.

user_28729220
Related