Einem Markierungsfeld einen Wert zuweisen / assigning a value to a checkbox field

Question

Level 1

0 points

Einem Markierungsfeld einen Wert zuweisen / assigning a value to a checkbox field

Hallo -

ich möchte einen Dienstplan erstellen. Die Arbeitsstunden einer Woche sollen aufaddiert werden. Für die übersichtliche Planung habe ich Markierungsfelder für die jeweiligen drei Schichten eines Tages. Jetzt möchte ich diesen Markierungsfelder jeweils einen Wert zuweisen, nämlich die Dauer der Schicht. Und dann möchte ich ein Summenfeld einrichten. Und zwar so:

Addiere die Werte aller markierten Felder! - (und ignoriere die Werte der nicht markierten Felder!).

Geht das?

- und falls ja: Wie?

My appologies, it´s hard enough to explain in my native language.... Let me try. I´m creating a sheet for work scheduling. I have the week in a row. Every day has three shifts. I´m using checkboxes to plan with a clear structur. I want to get the total of weekly hours. Therefore I´d like to assign a value to the checkbox fields (the hours per shift). And than I´d like to tell antoher field to get the sum:

Add up the values of all marked fields! - (and ignore all unmarked fields.)

How can I do this???

Danke!

Helma

Posted on Jul 27, 2015 5:08 AM

Reply

Answer 1

Best reply

Hiroto

Level 5

7,461 points

Jul 27, 2015 6:58 AM in response to helmapelz

Hello

You may try something like this. X:AR are auxiliary columns to convert boolean value to 0/1. Shift hours are defined in separate table.

Table 1 (excerpt) - in English, decimal period locale W3 =SUMPRODUCT(X3:AR3,Const::A$3:U$3) X3 =IF(B3,1,0) Y3 =IF(C3,1,0) Z3 =IF(D3,1,0) AA3 =IF(E3,1,0) AB3 =IF(F3,1,0) AC3 =IF(G3,1,0) AD3 =IF(H3,1,0) AE3 =IF(I3,1,0) AF3 =IF(J3,1,0) AG3 =IF(K3,1,0) AH3 =IF(L3,1,0) AI3 =IF(M3,1,0) AJ3 =IF(N3,1,0) AK3 =IF(O3,1,0) AL3 =IF(P3,1,0) AM3 =IF(Q3,1,0) AN3 =IF(R3,1,0) AO3 =IF(S3,1,0) AP3 =IF(T3,1,0) AQ3 =IF(U3,1,0) AR3 =IF(V3,1,0)

Table 1 (excerpt) - in German, decimal comma locale W3 =SUMMENPRODUKT(X3:AR3;Const::A$3:U$3) X3 =WENN(B3;1;0) Y3 =WENN(C3;1;0) Z3 =WENN(D3;1;0) AA3 =WENN(E3;1;0) AB3 =WENN(F3;1;0) AC3 =WENN(G3;1;0) AD3 =WENN(H3;1;0) AE3 =WENN(I3;1;0) AF3 =WENN(J3;1;0) AG3 =WENN(K3;1;0) AH3 =WENN(L3;1;0) AI3 =WENN(M3;1;0) AJ3 =WENN(N3;1;0) AK3 =WENN(O3;1;0) AL3 =WENN(P3;1;0) AM3 =WENN(Q3;1;0) AN3 =WENN(R3;1;0) AO3 =WENN(S3;1;0) AP3 =WENN(T3;1;0) AQ3 =WENN(U3;1;0) AR3 =WENN(V3;1;0)

Notes.

Formula in X3 can be filled down and right across X3:AR5.

Formula in W3 can be filled down.

You may hide X:AR

Tables are built with Numbers v2.

Good luck,

H

Reply

Answer 2

helmapelz Author

Level 1

0 points

Jul 27, 2015 9:16 AM in response to Hiroto

Thanks a lot!!! I understand your idea, but how to confuses me. This

Table 1 (excerpt) - in German, decimal comma locale

W3 =SUMMENPRODUKT(X3:AR3;Const::A$3:U$3)

X3 =WENN(B3;1;0)

.......

is just like a new language. Butr howwever, I´ll start and find it out!

Best, Corinna

Reply

Answer 3

Jul 27, 2015 9:27 AM in response to helmapelz

Try this method:

Create the table for "Value" as shown :

START>

	100
	10
	1

<STOP

To copy the table, click at the line "START>" (just after the ">") then shift click just before the "<", copy, then paste into your Sheet inNumbers

W3=Value::$B$1×COUNTIF(UNION.RANGES(FALSE,T3,Q3,N3,K3,H3,E3,B3), TRUE)+Value::$B$2×COUNTIF(UNION.RANGES(FALSE,C3,F3,I3,L3,O3,R3,U3), TRUE)+Value::$B$3×COUNTIF(UNION.RANGES(FALSE,D3,G3,J3,M3,P3,S3,V3), TRUE)

this is shorthand for... select cell W3, then type (or copy and paste from here) the formula:

=Value::$B$1×COUNTIF(UNION.RANGES(FALSE,T3,Q3,N3,K3,H3,E3,B3), TRUE)+Value::$B$2×COUNTIF(UNION.RANGES(FALSE,C3,F3,I3,L3,O3,R3,U3), TRUE)+Value::$B$3×COUNTIF(UNION.RANGES(FALSE,D3,G3,J3,M3,P3,S3,V3), TRUE)

this:

adds up the yellow checkboxes and multiplies by the value in the table Value corresponding to yellow (in this case, 100), then

adds up the green checkboxes and multiplies by the value in the table Value corresponding to green (in this case, 10), then

adds up the blue checkboxes and multiplies by the value in the table Value corresponding to blue (in this case, 1)

now select W3, copy

select W3 thru the end of the column, paste

Reply

Answer 4

helmapelz Author

Level 1

0 points

Jul 27, 2015 11:47 AM in response to Wayne Contello

Hey Wayne -

thanks! I like your method a lot. I made it so far:

COUNTIF is quite clear, in german: ZÄHLENWENN. But I´m not sure, how to get your UNION.RANGES. What is it? I tried it with SUMME, which means sum or total. But it´s not working yet. Additionally, what´s „compact left”??

It´s not working - or self-translating- when only copying and pasting.

Looking forward, best, Helma

Reply