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

#### soychristophe

##### New Member
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
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
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
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
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
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

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^^^
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
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
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.

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.

Replies
4
Views
81
Replies
1
Views
94
Replies
1
Views
156
Replies
1
Views
76
Replies
2
Views
125

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.

### Which adblocker are you using?

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

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