Funzione se nidificata con somma di valori di celle non attigue

Buongiorno a tutti.
Vorrei chiedere il vostro aiuto in merito ad un problema che proprio non riesco a risolvere.

Nello specifico ho creato una tabella presenze del personale basandomi su un calendario perpetuo (presente=1 - assente=vuoto/0), dove mensilmente la tabella si adatta al mese di riferimento (mi riferisco ai giorni Lun., Mar, Mer. ecc..

Ora io vorrei poter sommare i valori di presenza suddividendo in giornate feriali, sabati e domeniche/festivi (premetto, quindi, che le celle hanno un passo costante es: G5;H5;I5;J5;K5;L5;M5;N5;O5;P5;Q5;R5;S5;T5;A5:U5 (in grassetto le celle che devono rientrare nelle somme, le altre ovviamente non dovranno essere comprese nel calcolo delle somme).

Questo calcolo vorrei però poterlo impostare in modo tale che al cambiare del mese di riferimento e quindi dei giorni, le formule vadano comunque a calcolare i giorni feriali, sabati e domeniche/festivi senza doverle modificare ad ogni inizio mese.

Per fare questo credo di dover impostare una funzione SE nidificata ma, nonostante una miriade di tentativi e formule varie, il risultato non è mai quello desiderato.


Credete di riuscire a darmi una mano?

Grazie
Mauro

Ciao,
questa ti conta le presenze della prima settimana
se non è sabato o domenica
è matriciale ctrl shift invio

---

=SOMMA(SE(GIORNO.SETTIMANA(G7:AP7;2)<6;G9:AP9)

---

variala come vuoi
anche se ho dubbi che con le celle unite che hai funzioni a dovere

Quindi tu ritieni che tutte le mie dificoltà nel riuscire a sommare i valori siano dovuti solamente al fatto che le prime righe riportano delle celle unite (anche se son stato un pò costretto a realizzarlo così per via della forma differente di calcolo degli orari).

Per quanto riguarda la tua formula, c'è solo un inconveniente che ho già riscontrato in tutti i miei tentativi, purtroppo tiene conto di tutti i valori immessi su quella riga mentre le colonne denominate R.R. ed R.I. non devono rientrare nelle somme (ecco perchè ho aperto la discussione parlando di "sequenza a passo costante").

ciao
avevo approcciato prima al problema ma poi mi sono dovuto allontanare, per i sabato e domenica, propongo qualcosa di simile a quella di Federico:
=MATR.SOMMA.PRODOTTO(($G$7:$GJ$7>0)*(GIORNO.SETTIMANA($G$7:$GJ$7;2)>5)*$G9:$GJ9)
per i festivi, :
=MATR.SOMMA.PRODOTTO(--(CONTA.SE($G$7:$GJ$7;Celendario!$R$11:$R$27)=1)*$G9:$GJ9)

per i feriali:
=MATR.SOMMA.PRODOTTO(($G$7:$GJ$7>0)*(GIORNO.SETTIMANA($G$7:$GJ$7;2)<6)*$G9:$GJ9)

sorge però il problema di come vuoi considerare i festivi, qualora ricadessero di sabato o domenica.

EDIT
ho modificato le formule

Ciao, ti ringrazio per l'aiuto.
Rispondo subito alla tua domanda.
Se la giornata festiva cade di domenica il problema non si pone mentre se la festività cade di sabato dovrebbe essere conteggiata nelle giornate festive ecco perchè la mia intenzione, qualora sia possibile, sarebbe quella di impostare le formule con le condizioni. Stessa cosa dicasi se una giornata feriale si trasforma in festiva deve ricadere nel conteggio festivo così come detto poco prima per il sabato.

Credete non sia possibile far funzionare le vostre formule inserendo al loro interno le condizioni per il sabato, la domenica ed i festivi?

ciao
correggo le formule di prima, considerando i festivi sempre festivi, i sabati e domeniche solo quando non ricadono nei giorni fstivi, il resto feriale:
in GK9:
=MATR.SOMMA.PRODOTTO(($G$7:$GJ$7>0)*(GIORNO.SETTIMANA($G$7:$GJ$7;2)<6)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GJ$7)<>1)*$G9:$GJ9)

in GL9:
=MATR.SOMMA.PRODOTTO(($G$7:$GJ$7>0)*(GIORNO.SETTIMANA($G$7:$GJ$7;2)>5)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GJ$7)<>1)*$G9:$GJ9)

in GM9:
=MATR.SOMMA.PRODOTTO(--(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GJ$7)=1)*$G9:$GJ9)

e ricopi in basso

fai sapere dopo il test

ninai,
ho provato le tue ultime funzioni.
Sembrano funzionare ma le domeniche me le somma con i sabati mentre dovrebbero andare con i festivi.

Edit
Continua il test ho notato che nel mese di aprile, come detto sopra, le domeniche le somma nei sabati mentre nel mese di marzo le conta giustamente nei festivi.

ciao in base a questo

MrFama, 25/02/2016 14:36:

---

.....

Ora io vorrei poter sommare i valori di presenza suddividendo in giornate feriali, sabati e domeniche/festivi

---

avevo capito che volevi conteggiate insieme , sabati e domeniche


se in GL, vuoi solo i sabati non festivi, basta che modifichi il >5 in =6
pertanto:
=MATR.SOMMA.PRODOTTO(($G$7:$GJ$7>0)*(GIORNO.SETTIMANA($G$7:$GJ$7;2)=6)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GJ$7)<>1)*$G9:$GJ9)



a questo punto le domeniche dove le vuoi contggiate?? nei festivi??? o dove???

forse ho capito, per festivi più domeniche non festive, in GM9:
=MATR.SOMMA.PRODOTTO(--(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GJ$7)=1)*$G9:$GJ9)+MATR.SOMMA.PRODOTTO(($G$7:$GJ$7>0)*(GIORNO.SETTIMANA($G$7:$GJ$7;2)=7)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GJ$7)<>1)*$G9:$GJ9)

GRANDE, sembra funzionare tutto alla perfezione ma...prima di chiudere la discussione definitivamente, se per te e la community va bene, preferirei prendermi un pò di tempo per verificare il tutto con calma....

ok, è giusto che testi per bene.
Io riscontro che nei mesi con meno di 31 giorni, le formule vanno in errore, a causa delle formule in nella riga 7 (da FS7).
Io ho ovviato con, in M7:
=SE(MESE(G7+1)=MESE($A$6);G7+1;0)

in M6:
=SE(M7;M7;"")

trascinate a destra fino alla fine.
unica accortezza, nascondere gli zeri a fine mese febbraio oppure aprile, giugno, settembre, novembre. (con formato personalizzato tipo: g;-0;;@. , con formattazione condizionata, da opzioni)

Si avevo notato anche io questo problema ma stavo testando le tue formule accorciando proprio le formule.

Adesso provo a fare anche questa modifica che mi hai suggerito.
Per quanto riguarda il lasciare le celle vuote quando il mese è più corto il file è già impostato così.
Mi sorge solo un dubbio, al momento dalla cella del giorno 29 fino al 31 ho inserito queste formule:
=SE(MESE(FM7+1)=MESE(A6);FM7+1;"")
=SE(MESE(FM7+1)=MESE(A6);FM7+2;"")
=SE(MESE(FM7+1)=MESE(A6);FM7+3;"")
proprio per far si che rimangano vuote.
Trascinando le formule che mi hai suggerito la funzionalità è la stessa oppure devo fermarmi con il trascinamento alla cella FM7?

ciao
NO!
devi andare fino al 31 giorno (GJ7).

per nascondere gli eventuali zeri negli ultimi giorni, usa il formato personalizzato: g;-0;;@.

ti faccio sapere l'esito delle mie prove....grazie ancora

Buongiorno,
sto testando tutto e da una prima analisi, dopo aver modificato il calendario secondo le indicazioni, le formule sembrano funzionare perfettamente. Unica cosa che ho constatato errata, fino ad ora, è che la giornata festiva del Patrono (indicata alla cella R27 del Calendario) non viene conteggiata come festivo ma come giornata feriale normale.

Continuo con i miei test e vi terrò aggiornati.

MrFama, 26/02/2016 09:01:

---

Unica cosa che ho constatato errata, fino ad ora, è che la giornata festiva del Patrono (indicata alla cella R27 del Calendario) non viene conteggiata come festivo ma come giornata feriale normale.

---

nel mio file risulta giusto:

patrono il 02/05/2016


per il mese di maggio mi risultano:

6 giorni festivi (4 domeniche + primo maggio + patrono)

4 sabati

21 feriali

dove è l'errore???

NB
da notare che la prima domenica coincide con il 1° maggio, pertanto viene conteggiata solo una volta

Infatti....adesso anche a me da il risultato corretto. Non so come mai prima contava male...forse le troppe prove che sto facendo hanno fatto "sbarellare" anche il file e non solo la mia testa

Ninai la formula funziona fino alla riga 28, dalla riga 29 (non essendo più una cella unita ma 3 celle separate con conteggi diversi), la formula smette di funzionare.

il riferimento dovrebbe essere sulla cella centrale (per intenderci H - N- ecc.), dove andrò ad inserire gli orari. Questo non è vincolante...si potrebbe anche modificare la prima colonna mettendo 0 e 1, credi che si possa fare?

ciao
quello che manifesti, secondo me è un'anomalia nella tabella, cambiando la struttura e la tipologia di dati è come se fosse un'altra tabella.
Comunque, se ho ben interpretato (non posso sapere di preciso cosa conterranno le celle), devi modificare le tre formule partendo dalla riga 29, e precisamente:

in GK29:
=MATR.SOMMA.PRODOTTO(($G$7:$GE$7>0)*(GIORNO.SETTIMANA($G$7:$GE$7;2)<6)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GE$7)<>1)*($H29:$GF29<>""))

in GL29:
=MATR.SOMMA.PRODOTTO(($G$7:$GE$7>0)*(GIORNO.SETTIMANA($G$7:$GE$7;2)=6)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GE$7)<>1)*($H29:$GF29<>""))


in GM29:
=MATR.SOMMA.PRODOTTO(--(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GE$7)=1)*($H29:$GF29<>""))+MATR.SOMMA.PRODOTTO(($G$7:$GE$7>0)*(GIORNO.SETTIMANA($G$7:$GE$7;2)=7)*(CONTA.SE(Celendario!$R$11:$R$27;$G$7:$GE$7)<>1)*($H29:$GF29<>""))


il tutto, presupponendo che si vogliano conteggiare le colonne H,N,T.......GF, che non siano vuote

Perfetto, il calcolo nelle celle lo effettuo proprio a partire dalla cella H in avanti.

Secondo te, in questa tabella, se volessi calcolare le ore di lavoro da da dover fare, in base ai giorni di presenza (considerando sempre il calendario), sarebbe possibile considerando come orari:
lunedì: 6 ore
martedì: 6 ore
mercoledì: 6 ore
giovedì: 6 ore
venerdì: 6 ore
sabato: 6 ore
domenica: 0 ore

es. giorni lavorativi 21, giorni di presenza 20, in base ai giorni calcolare quante ore dovrei fare al lavoro

Ma è già fatto. Basta sommare i sabati più i feriali e moltiplicare per 6.
=(GK9+Gl9)*6

Ciao ninai,
eccomi a ragguagliarti.....tutto sembra funzionare alla grande.
Per quanto riguarda la mia ultima domanda....l'avevo fatta ma....continuando a lavorare sul file avevo anche trovato la soluzione ^_^, comunque grazie anche per la tua ultima risposta ^_^

Credo quindi di poter dire che tutto funziona alla grande.
Ti ringrazio per il tuo fondamentale aiuto e la tua disponibilità.

Adesso vorrei provare con un ulteriore step, se fosse possibile (una userform con cui inserendo qualifica, cognome e nome mi compila un format estraendo i dati dalla tabella).