sum product with conditional start date and end date / end date blank failure

soychristophe

New Member
Joined
May 9, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
Hello Community, I have a block from which I can't get out....

I am using the formula sumaproduct to get the data of the sheet Absences with its first day and its last day, the problem I get when in the last day data there is no date, it is understood or it could be calculated with the formula today() to give you a data. The question is that when there is no date, the summaproduct that I have made adds up the total of days and even in a month that there is no initial or final date.

How can I solve it?

Thank you very much


 

Attachments

  • CONSULTA.jpg
    CONSULTA.jpg
    93.4 KB · Views: 5

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,607
Office Version
  1. 365
Platform
  1. Windows
For some reason your mini sheet didn't post correctly so I can't copy the formula from it.

Instead of using NOT(ISBLANK(....)) try (Absences_Low[Last day of absence]<>"")
 

soychristophe

New Member
Joined
May 9, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
CONSULTA.xlsx
ABCDE
1ID de empleadoFecha inicio informeFecha final informeMES INFORMEDias Ausentes (bajas)
2AA01/01/202131/01/2021Enero83
3AA01/02/202128/02/2021Febrero69
4AA3/1/213/31/21Marzo69
5
6
7ID de empleadoPrimer día de ausenciaÚltimo día de ausenciaDías
8AA1/1/211/14/2114
9AA3/2/2169
Datos
Cell Formulas
RangeFormula
E2:E4E2=SUMPRODUCT( (AUSENCIAS_BAJAS[Días])* (AUSENCIAS_BAJAS[ID de empleado]=[@[ID de empleado]])* (AUSENCIAS_BAJAS[Primer día de ausencia]>=[@[Fecha inicio informe]]* (AUSENCIAS_BAJAS[Último día de ausencia]<=[@[Fecha final informe]])* NOT(ISBLANK((AUSENCIAS_BAJAS[Último día de ausencia])))))
D8:D9D8=IF(([@[Último día de ausencia]]-[@[Primer día de ausencia]]+1)<1, (TODAY()-[@[Primer día de ausencia]]+1), ([@[Último día de ausencia]]-[@[Primer día de ausencia]]+1))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,607
Office Version
  1. 365
Platform
  1. Windows
Excel Formula:
=SUMPRODUCT(AUSENCIAS_BAJAS[Días]*(AUSENCIAS_BAJAS[ID de empleado]=[@[ID de empleado]])*(AUSENCIAS_BAJAS[Primer día de ausencia]>=[@[Fecha inicio informe]])*(AUSENCIAS_BAJAS[Último día de ausencia]<=[@[Fecha final informe]])*(AUSENCIAS_BAJAS[Último día de ausencia]<>""))
 

soychristophe

New Member
Joined
May 9, 2021
Messages
3
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Excel Formula:
=SUMPRODUCT(AUSENCIAS_BAJAS[Días]*(AUSENCIAS_BAJAS[ID de empleado]=[@[ID de empleado]])*(AUSENCIAS_BAJAS[Primer día de ausencia]>=[@[Fecha inicio informe]])*(AUSENCIAS_BAJAS[Último día de ausencia]<=[@[Fecha final informe]])*(AUSENCIAS_BAJAS[Último día de ausencia]<>""))

Thank you very much jasonb75 you have helped me a lot.

I was close to the result, but I got blocked.

Now looking at the result one last question, if in case there is no end date, that the formula returns me the data by calculating EOMONTH date,
how could it be done?

thank you very much


CONSULTA_mini.xlsx
ABCDEFGHIJ
1ID de empleadoFecha inicio informeFecha final informeMES INFORMEresultResult
2AA1/1/211/31/21Enero14<<<===14
3AA2/1/212/28/21Febrero0<<<===0
4AA3/1/213/31/21Marzo0<<<===30or69
5
6^^^
7^^^
8^^^
9^^^
10ID de empleadoPrimer día de ausenciaÚltimo día de ausenciaDías del MesActualidad^^^
11AA1/1/211/14/211414>>>>>>>>>>>>>^^^
12AA3/2/213069>>>>>>>>>>>>>^^^
Hoja1
Cell Formulas
RangeFormula
E2:E4E2=SUMPRODUCT( AUSENCIAS_BAJAS[Días del Mes]*(AUSENCIAS_BAJAS[ID de empleado]=[@[ID de empleado]])* (AUSENCIAS_BAJAS[Primer día de ausencia]>=[@[Fecha inicio informe]])* (AUSENCIAS_BAJAS[Último día de ausencia]<=[@[Fecha final informe]])* (AUSENCIAS_BAJAS[Último día de ausencia]<>""))
D11:D12D11=IF(([@[Último día de ausencia]]-[@[Primer día de ausencia]]+1)<1, (EOMONTH([@[Primer día de ausencia]],0)-[@[Primer día de ausencia]]+1), ([@[Último día de ausencia]]-[@[Primer día de ausencia]]+1))
E11:E12E11=IF(([@[Último día de ausencia]]-[@[Primer día de ausencia]]+1)<1, (TODAY()-[@[Primer día de ausencia]]+1), ([@[Último día de ausencia]]-[@[Primer día de ausencia]]+1))
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,607
Office Version
  1. 365
Platform
  1. Windows
Try this one
Excel Formula:
=SUMPRODUCT(AUSENCIAS_BAJAS[Días del Mes]*(AUSENCIAS_BAJAS[ID de empleado]=[@[ID de empleado]])*(AUSENCIAS_BAJAS[Primer día de ausencia]>=[@[Fecha inicio informe]])*(AUSENCIAS_BAJAS[Último día de ausencia]<=[@[Fecha final informe]])*(AUSENCIAS_BAJAS[Primer día de ausencia]<=[@[Fecha final informe]]))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,670
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: sum product with conditional start date and end date / end date blank failure
also sum product with conditional start date and end date / end date blank failure
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,141,871
Messages
5,709,097
Members
421,614
Latest member
RAB29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top