Calc: What is the correct function for automated sum calculation when multiple IF- & one then- factor is necessary?

[Ghita_gmkl]

(upd. 20.8.2014 15:51 GMT+2 just edited the subject to be better -G-)
Hi,

It is years ago since I was working with Excel-tables. Can anyone help me with a function? I am unfortunately not certain whether the terms are correct, but issue is this:

I have in one column three options according to which I want the numbers in the next column to be summed up in one cell. I copy below the text format on what I want. Can you understand me based on that? There are two functions that I need.

1. (first)

IF C-column= Ak then SUM in cell D70, , range: 9-67 , (new row)

IF C-column = Va then SUM in cell D69, range: 9-67

2. (second) (ps. I write the number two, three or any number but preview want to force it to number one. This is the second - so it is - nr “two”)

IF B-column= A and C-column = Va then sum of D-column into cell E3, IF B-column= A and C-column = Ak then sum of D-column in cell F3, range 9-67

IF B-column = L and C-column = Va then sum of D-column into cell E4, IF B-column = L and C-column = Ak then sum of D-column in cell F4, range 9-67

IF B-column = P and C-column = Va then sum of D-column into cell E5, IF B-column = P and C-column = Ak then sum of D-column in cell F5, range 9-67

Below a small copy of the table columns. I hope they didnt get messed up during posting.

• List item

Below are three columns to which the appropriate sums of the combination A/L/P and Va/Ak should automatically be calculated from cells like the columns copied second.

Sum/month Varalla-olot (Va) Aktiivi-tunnit (Ak)
Arki (A)
Lauantai (L)
Pyhä (su tai arkipyhä) (P)

• List item

Below are four columns; Day, A/L/P, Va/Ak and Sum from which the sums should be copied and summed up in a total sum according to the combination of A/L/P and Va/Ak. A, L, P are the weekdays Sum is added manually when on the go. I had to remove the example rows since it got messed up (according to the preview here). Each row has either A, L, or P and Va or AK

Day A/L/P Va/Ak Sum

I am very grateful if someone could help me in this. Looking forward in seeing a reply.

Ghita

ps. the capthcas are at times very difficult to see

[mariosv]

If I understand your question, please find attached a sample file with several ways to sum conditionally:

• SUMIF(), condition on one column.
• SUMIFS(), condition on several columns.
• SUMPRODUCT(), condition(s) on one/several column(s).
• Pivot Table plus GETPIVOTDATA(), condition on one/several column(s).

Samples of every function

[Ghita_gmkl]

Thank you for this. I think I will get a hang of it by the help of this.

This is just to inform. I don’t know is it only my computer doing it. The link Samples of every function.
In Internet Explorer (v.11.0.9600.17239) when I wanted to get the content the link show that it is a *.ods-file, but when downloading it becomes a zip-file. And the extracted content is then according to that.

In chrome download as an *.ods ok. I don’t know whether it is setting related issue or something else. Maybe good to verify by someone else?

[Ghita_gmkl]

Yes. This was exactly what I needed. Thank you very much!

[mariosv]

I think it is a Internet Explorer issue.

[Ghita_gmkl]

I would like to add an additional continuation to this and that is a timecondition with automatic calculations. How do I best add the conditions to the pivot, or is a third option recommended? The additional question will be posted under this subject:

Calc: Timecondition - automatic hour-calculation from changing 24hr-clock (ex. 16-9 from which 16-24 =8 and 00-9 =9), with additional 2-4 changing conditions of which one is the date-cell.

I assume that I will first need an additional calculation of the hours before getting the inactual time-condition added in to the Pivot, or equivalent?

The condition is as an addition to the already existent Va/Ak & A/L/P.

[mariosv]

Please attach/share a sample file with the new options, or detail a couple data lines with what you want.