Excel Forum

	Soluzioni Excel Domande e Risposte
		SOMMA.SE intervallo date, errore sintassi??

Pagina precedente | 1 | Pagina successiva

Vota | Stampa | Notifica email

« Discussione Precedente | Lista | Discussione Successiva »

Autore

SOMMA.SE intervallo date, errore sintassi??

Ultimo Aggiornamento: 15/09/2020 01:22

curl p1 bamboo

Post: 1

Registrato il: 22/11/2009

Città: MILANO

Età: 37

Utente Junior

excel 2010

OFFLINE

12/09/2020 18:57

00#1

Buon pomeriggio.
Sto realizzando un programmino (excel 2010)in grado di gestire le prenotazioni per ombrelloni cabine e gazebi di una spiaggia.
Mi trovo in difficoltà quando voglio fare il conto di quanto costa l'eventuale soggiorno.
Ho trovato googlando, la formula SOMMA.SE che farebbe al caso mio, per la quale, tra l'altro è stato aiutato un utente qualche giorno fa 🙈.

la formula di partenza, è questa:
=SOMMA.SE(J:J;">="&PRENOTAZIONI!D7;AH:AH)-SOMMA.SE(J:J;">"&PRENOTAZIONI!E7;AH:AH)

nella colonna J si trovano le date in ordine crescente della stagione estiva
nel foglio prenotazioni nella cella D7 si trova la data di inizio soggiorno
nella colonna AH si trovano i costi al giorno dell'item scelto(ombrellone cabina tenda ecc)
nel foglio prenotazioni nella cella E7 si trova la data di fine soggiorno
La formula sopra riportata mi somma gli importi di AH:AH per i giorni che trova in J:J corrispondenti alle prenotazioni che si trovano in D7 E7(rispettivamente x inizio e fine soggiorno).
Sarei a posto se non fosse che l'intervallo dove si trovano gli importi è variabile in base all'item prenotato.
Devo quindi adattare la formula spostando il range di INT_SOMMA (attuale colonna AH:AH) in base all'item richiesto, anch'esso reperibile nel foglio prenotazioni (cella B7)

la mia difficoltà sta nella maledettissima sintassi, almeno credo 🙈

per semplificare possiamo vedere anche solo la prima parte della formula, poi la adatto io.
=SOMMA.SE(J:J;">="&PRENOTAZIONI!D7;AH:AH)
attraverso un CERCA.ORIZZ trovo l'item prenotato nella solita tabella, identifico la colonna, metto tutto sotto forma di formula ma NISBA. non funziona..sotto la formula:
=CERCA.ORIZZ(PRENOTAZIONI!B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)
nel foglio prenotazioni in B7 ho l'item prenotato
nel foglio conteggi ho la tabella con gli importi, sopra ogni colonna è riportato il nome dell'item per permettere al CERCA.ORIZZ di funzionare range Y11:KO12. sulla riga 12 estraggo il nome delle colonne tipo AH:AH

vorrei, per arrivare al quesito, chiedervi come correggere la formula, che poi ripeterò nella colonna S del foglio prenotazioni per arrivare ad evidenziare il totale del soggiorno.
la formula secondo me dovrebbe essere:
=SOMMA.SE(J:J;">="&PRENOTAZIONI!D7;CERCA.ORIZZ(PRENOTAZIONI!B7;CONTEGGI!$Y$11:$KO$12;2;FALSO))
ma quando la scrivo ricevo un errore di sintassi
se invece separo il SOMMA.SE ed il CERCA.ORIZZ su due celle diverse e poi collego le formule così:
= SOMMA.SE(J:J; ">="&PRENOTAZIONI!D8;AH12) -SOMMA.SE(J:J; ">" & PRENOTAZIONI!E8;AH12)
ottengo 0 (in AH12 c'è appoggiato il CERCA.ORIZZ)

allego il file, ho nascosto i fogli inutili alla domanda.
Grazie in anticipo a chiunque proverà a darci un occhio
Daniel

Download

[Modificato da curl p1 bamboo 12/09/2020 18:57]

federico460

Post: 1.944

Registrato il: 10/10/2013

Città: VICENZA

Età: 69

Utente Veteran

365

OFFLINE

12/09/2020 19:41

00#2

ciao
non ne ho capito molto
me se devi arrivare a 6

=MATR.SOMMA.PRODOTTO((CONTEGGI!$J$13:$J$164=$D7)*(CONTEGGI!$Y$11:$KO$11=$B7);CONTEGGI!$Y$13:$KO$164)

e non ti serve la riga con HH ecc.

in

[Modificato da federico460 12/09/2020 19:42]

ABCDEF@Excel

12/09/2020 20:13

00#3

Stai attento in F16, la formula non trova nulla in riga 8 ...
Scrivo minore per non mettere il carattere <
Dovresti usare il somma.piu.se. In F16 =SOMMA.PIÙ.SE(AH:AH;J:J;">="&PRENOTAZIONI!D7;J:J;"minore"&PRENOTAZIONI!E7)
Io preferisco il =MATR.SOMMA.PRODOTTO(($J$13:$J$164>=PRENOTAZIONI!D7)*($J$13:$J$164minorePRENOTAZIONI!E7)*($AH$13:$AH$164))

EDIT Se per caso devi usare la cella G25 =SOMMA.PIÙ.SE(INDIRETTO(G25);J:J;">="&PRENOTAZIONI!D7;J:J;"minore"&PRENOTAZIONI!E7)

NB. Dire a una formula "calcola tutta" la colonna è dispensioso per i calcoli
Altre celle che non vanno?

@federico460
Penso che la formula vada in Conteggi... F16
Comunque penso che dal 01/05/20 al 06/05/20 siano 5 giorni

Download

[Modificato da ABCDEF@Excel 12/09/2020 20:31]

curl p1 bamboo

Post: 2

Registrato il: 22/11/2009

Città: MILANO

Età: 37

Utente Junior

excel 2010

OFFLINE

12/09/2020 23:00

00#4

Re:

ABCDEF@Excel, 12/09/2020 20:13:
Stai attento in F16, la formula non trova nulla in riga 8 ...

Scrivo minore per non mettere il carattere <
Dovresti usare il somma.piu.se. In F16 =SOMMA.PIÙ.SE(AH:AH;J:J;">="&PRENOTAZIONI!D7;J:J;"minore"&PRENOTAZIONI!E7)
Io preferisco il =MATR.SOMMA.PRODOTTO(($J$13:$J$164>=PRENOTAZIONI!D7)*($J$13:$J$164minorePRENOTAZIONI!E7)*($AH$13:$AH$164))

EDIT Se per caso devi usare la cella G25 =SOMMA.PIÙ.SE(INDIRETTO(G25);J:J;">="&PRENOTAZIONI!D7;J:J;"minore"&PRENOTAZIONI!E7)

NB. Dire a una formula "calcola tutta" la colonna è dispensioso per i calcoli
Altre celle che non vanno?

@federico460
Penso che la formula vada in Conteggi... F16
Comunque penso che dal 01/05/20 al 06/05/20 siano 5 giorni

Grazie x la risposta. In realtà alcune delle celle che avete preso in considerazione erano per prove che avevo fatto per risolvere il problema.
Ho trovato una soluzione con i suggerimenti utilizzando la formula INDIRETTO(). ✌
Però se la metto nel foglio dove si trova la tabella delle prenotazioni nel foglio PRENOTAZIONI, non funziona. se invece la metto nel foglio CONTEGGI funziona tutto.
ri allego il file modificato con dei colori per aiutarvi a capire. (ho nascosto delle colonne per rendere il file più leggibile).

Le formule in via definitiva dovrebbero stare nel foglio PRENOTAZIONI in colonna S:S

qui sotto la formula modificata con i vostri suggerimenti e riadattata al mio file:

=SOMMA.SE(CONTEGGI!J:J;">="&PRENOTAZIONI!D7;INDIRETTO(CERCA.ORIZZ(PRENOTAZIONI!B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))-SOMMA.SE(CONTEGGI!J:J;">"&PRENOTAZIONI!E7;INDIRETTO(CERCA.ORIZZ(PRENOTAZIONI!B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))

Daniel

Download (1.452 KB)

ABCDEF@Excel

13/09/2020 01:50

00#5

Foglio Prenotazioni S7 =SOMMA.SE(CONTEGGI!J:J; ">="&D7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO))) -SOMMA.SE(CONTEGGI!J:J; ">" & E7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))

Se in Foglio Conteggi cambi Y12 in =INDIRIZZO(13;RIF.COLONNA();4)&":"&INDIRIZZO(200;RIF.COLONNA();4) e trascini a destra

In Prenotazioni S7 =SOMMA.SE(CONTEGGI!$J$13:$J$200; ">="&D7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO))) -SOMMA.SE(CONTEGGI!$J$13:$J$200; ">" & E7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))

Risparmi cpu e attesa da oltre 1048.000 ad 190 righe
EDIT Come scritto da 01/05/20 al 06/05/20 sono 5 giorni, al secondo... -SOMMA.SE(CONTEGGI!J:J; ">" devi mettere ">="

[Modificato da ABCDEF@Excel 13/09/2020 01:56]

curl p1 bamboo

Post: 3

Registrato il: 22/11/2009

Città: MILANO

Età: 37

Utente Junior

excel 2010

OFFLINE

13/09/2020 02:36

00#6

Re:

ABCDEF@Excel, 13/09/2020 01:50:
Foglio Prenotazioni S7 =SOMMA.SE(CONTEGGI!J:J; ">="&D7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO))) -SOMMA.SE(CONTEGGI!J:J; ">" & E7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))

Se in Foglio Conteggi cambi Y12 in =INDIRIZZO(13;RIF.COLONNA();4)&":"&INDIRIZZO(200;RIF.COLONNA();4) e trascini a destra

In Prenotazioni S7 =SOMMA.SE(CONTEGGI!$J$13:$J$200; ">="&D7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO))) -SOMMA.SE(CONTEGGI!$J$13:$J$200; ">" & E7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))

Risparmi cpu e attesa da oltre 1048.000 ad 190 righe
EDIT Come scritto da 01/05/20 al 06/05/20 sono 5 giorni, al secondo... -SOMMA.SE(CONTEGGI!J:J; ">" devi mettere ">="

Grazieeee!
Ottimo, ora funziona non avrei detto che mi mancava un AND "conteggi!" 😂

accetto tutte le tue modifiche compresa quella x risparmiare sulle risorse del pc...ma non capisco una cosa.
qui sotto mi consigli di usare un >= al posto di un maggiore.

ABCDEF@Excel, 13/09/2020 01:50:

Come scritto da 01/05/20 al 06/05/20 sono 5 giorni, al secondo... -SOMMA.SE(CONTEGGI!J:J; ">" devi mettere ">="

Se scopri il foglio MENSILI nel range E20:J20 vedi la rappresentazione della prenotazione in appunto 5 giorni.
Io però avrei detto che se prenoti dal 01/05/20 al 06/05/20 anche il sesto giorno sarebbe compreso nella prenotazione.
Capisco che sia una domanda off topic, nel caso riapro altro post.

Come posso modificare questa formula?
(Il foglio prenotazioni e mensili vengono da un file già fatto che mi hanno passato e non capisco l'uso di queste formule)
=SE(MATR.SOMMA.PRODOTTO((Tabella1[ITEM]=$C20)*(Tabella1[DAL]<=I$10)*(Tabella1[AL]>I$10)*1)=1;INDICE(Tabella1[CLIENTE];CONFRONTA($C20;SE((Tabella1[AL]>I$10)*(Tabella1[DAL]<=I$10);Tabella1[ITEM];0);0));SE(MATR.SOMMA.PRODOTTO((Tabella1[ITEM]=$C20)*(Tabella1[DAL]<=I$10)*(Tabella1[AL]>I$10)*1)>1;"ERR";""))

ABCDEF@Excel

13/09/2020 13:35

00#7

Per indicare la cella con AH13:AH200, sarebbe =INDIRETTO("CONTEGGI!"&INDIRIZZO(12;CONFRONTA(B7;CONTEGGI!$A$11:$KO$11;0);4))
Però non riesco inserirla in SOMMA.PIÙ.SE ed in MATR.SOMMA.PRODOTTO, forse qualche altro esperto.

Il Foglio Mensile dice che hai fatto 5 giorni (giusto si pernotta solo 5 notti). La Tua formula con due SOMMA.SE non è giusta, perchè calcola 6g*6euro = 36 (dovrebbe essere 5g*6euro = 30). L'unico modo sarebbe di mettere >= nel secondo SOMMA.SE anche se non mi piace per nulla.

>>>Io però avrei detto che se prenoti dal 01/05/20 al 06/05/20 anche il sesto giorno sarebbe compreso nella prenotazione.
Se desideri, cerca nel forum oppure apri altro post. Io sono convinto che siano 5 giorni (ho alcuni dubbi riguardo il bagnino, basta vedere le Tue regole). File bello mà molto complesso, in PRENOTAZIONI cella P7 metterei =SE([@ITEM]="";"";formula già presente), invece in MENSILI cella D11 ci và una formula (non riesco trovare il "nesso" con le celle M1:Q1)

A proposito in Mensile E11 aggiungendo 3 volte = alla formula in (Tabella1[AL]>=J$10)*1)...MATRICIALE con CTRL + MAIUSC + INVIO... farà vedere 6 giorni, se sono le Tue regole... e comunque ci saranno presenti altre formule che riprendono il concetto dei 5g o 6g

[Modificato da ABCDEF@Excel 13/09/2020 13:42]

curl p1 bamboo

Post: 4

Registrato il: 22/11/2009

Città: MILANO

Età: 37

Utente Junior

excel 2010

OFFLINE

13/09/2020 16:24

00#8

Re:

ABCDEF@Excel, 13/09/2020 13:35:
Per indicare la cella con AH13:AH200, sarebbe =INDIRETTO("CONTEGGI!"&INDIRIZZO(12;CONFRONTA(B7;CONTEGGI!$A$11:$KO$11;0);4))
Però non riesco inserirla in SOMMA.PIÙ.SE ed in MATR.SOMMA.PRODOTTO, forse qualche altro esperto.

In realtà mi sembra funzionare bene così. Ho messo in S7 del foglio prenotazioni (e ho tirato giù):
=SE.ERRORE(SOMMA.SE(CONTEGGI!$J$13:$J$200;">="&D7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)))-SOMMA.SE(CONTEGGI!$J$13:$J$200;">"&E7;INDIRETTO("CONTEGGI!"&CERCA.ORIZZ(B7;CONTEGGI!$Y$11:$KO$12;2;FALSO)));"")

ABCDEF@Excel, 13/09/2020 13:35:

Il Foglio Mensile dice che hai fatto 5 giorni (giusto si pernotta solo 5 notti). La Tua formula con due SOMMA.SE non è giusta, perchè calcola 6g*6euro = 36 (dovrebbe essere 5g*6euro = 30). L'unico modo sarebbe di mettere >= nel secondo SOMMA.SE anche se non mi piace per nulla.
>>>Io però avrei detto che se prenoti dal 01/05/20 al 06/05/20 anche il sesto giorno sarebbe compreso nella prenotazione.
Se desideri, cerca nel forum oppure apri altro post. Io sono convinto che siano 5 giorni (ho alcuni dubbi riguardo il bagnino, basta vedere le Tue regole).

Essendo un progetto per uno stabilimento balneare, dovrei contare i giorni, non le notti. Ho perciò modificato la formula in E20 del foglio MENSILI così(usando dopo l'inserimento CTRL+SHIFT+ENTER per considerarlo un array, vedi parentesi graffe)

{=SE(MATR.SOMMA.PRODOTTO((Tabella1[ITEM]=$C20)*(Tabella1[DAL]<=E$10)*(Tabella1[AL]>=E$10)*1)=1;INDICE(Tabella1[CLIENTE];CONFRONTA($C20;SE((Tabella1[DAL]<=E$10)*(Tabella1[AL]>=E$10);Tabella1[ITEM];0);0));SE(MATR.SOMMA.PRODOTTO((Tabella1[ITEM]=$C20)*(Tabella1[DAL]<=E$10)*(Tabella1[AL]>=E$10)*1)>1;"ERR";""))}

ABCDEF@Excel, 13/09/2020 13:35:

File bello mà molto complesso,

GRAZIEEE! ;) purtroppo sono autodidatta..a scuola programmavo PLC, ma mai visto nessun linguaggio di programmazione, infatti mi picchio abbastanza anche con VBA.

ABCDEF@Excel, 13/09/2020 13:35:

in PRENOTAZIONI cella P7 metterei =SE([@ITEM]="";"";formula già presente), invece in MENSILI cella D11 ci và una formula (non riesco trovare il "nesso" con le celle M1:Q1)

Per quanto riguarda la cella P7 del foglio prenotazioni si tratta di un refuso. stavo provando a capire come conteggiare il costo del soggiorno, ma mi sono scontrato col fatto che la tabella di appoggio del foglio IMPOSTA ALTA - BASSA STAGIONE era fatta male. ne ho rifatta un'altra nel foglio CONTEGGI che abbiamo sistemato assieme. ancora grazie :)
in D11 del foglio MENSILI ci andava una formula per descrivere il tipo di servizio da offrire al cliente nel file originale che ho poi modificato. ci metterò altro e cancellerò il range J1:Q2

ABCDEF@Excel, 13/09/2020 13:35:

A proposito in Mensile E11 aggiungendo 3 volte = alla formula in (Tabella1[AL]>=J$10)*1)...MATRICIALE con CTRL + MAIUSC + INVIO... farà vedere 6 giorni, se sono le Tue regole... e comunque ci saranno presenti altre formule che riprendono il concetto dei 5g o 6g

Eh si, ho trovato la risposta stanotte dopo un po di prove. Sto facendo turni notturni e avevo del tempo a disposizione.

In generale a lavoro finito pensavo di caricare il file (magari senza la pubblicità della ditta) x capire se sia abbastanza user friendly. si può fare? ho visto che c'è una sezione "I VOSTRI LAVORI".
Ho anche un programmino in grado di gestire i rapportini di lavoro e la consuntivazione di ditte esterne in ambito di officina.
Grazie di tutto.
Daniel

ABCDEF@Excel

14/09/2020 23:45

00#9

Tu fai come desideri, dal canto mio valuto che parto da XXX per Te,
Se arrivo alle 0:00 di mattina e lascio alle 24:00? OK (calcoli giusti)e lascio il "bagno"

Io penso unicamente al "Motel & pensioni", arrivo alle ore 12:00 e cerco un "bagno\bagnino". Perchè dovrei pagare dalle 0:00 alle 12:00?
Lascio il "Motel & pensioni" alle 12:00, perchè dovrei pagare dalle 12:00 sino 24:00?

[Modificato da ABCDEF@Excel 14/09/2020 23:53]

curl p1 bamboo

Post: 5

Registrato il: 22/11/2009

Città: MILANO

Età: 37

Utente Junior

excel 2010

OFFLINE

15/09/2020 00:22

00#10

Re:

ABCDEF@Excel, 14/09/2020 23:45:
Tu fai come desideri, dal canto mio valuto che parto da XXX per Te,
Se arrivo alle 0:00 di mattina e lascio alle 24:00? OK (calcoli giusti)e lascio il "bagno"

Io penso unicamente al "Motel & pensioni", arrivo alle ore 12:00 e cerco un "bagno\bagnino". Perchè dovrei pagare dalle 0:00 alle 12:00?
Lascio il "Motel & pensioni" alle 12:00, perchè dovrei pagare dalle 12:00 sino 24:00?

Non ho capito.. 🙈
Il file è per prenotare e soggiornare in una spiaggia di uno stabilimento Balneare..puoi fare prenotazioni giornaliere e di più giorni.

ABCDEF@Excel

15/09/2020 01:22