Counting the days between values if another row has a specific value(text

Copper Contributor

Hey guys,

 

I'm looking to count the total amount of days between a lot of dates. but only if in another column a certain value is given. 

 

For example 

A 1-12-2020 31-06-2021

B 1-03-2021 5-05-2021

A 1-04-2018 10-08-2020

 

In this situation I would like to know the total amount of days between the dates with A in front of them. How do I do this?

 

Thanks in advance!

6 Replies

@ietsverzinnen 

=SUMPRODUCT((A2:A8="A")*(C2:C8-B2:B8))

 

Maybe with this formula as shown in the attached file.

Hey Quadruple_Pawn,

Thanks for your reply!
Somehow it doesn't work for me, it gives me a value error when I use: 

=SUMPRODUCT((B:B=S4)*(I:I-E:E))

 

=SUMIFS(C:C,A:A,"=A")-SUMIFS(B:B,A:A,"=A")
By the way, the example 31-06-2021 is not a correct date. Maybe that will cause error also.

@ietsverzinnen 

=SUMPRODUCT((B:B="S4")*(I:I-E:E)

 

It works in my spreadsheet if i enter "S4" instead of S4 in the formula.

Hey Quadruple_Pawn,

S4 in this case was a direction to a cell(that had the wanted value). This solution somehow still gives a value error, although I don't understand how. No matter though, I ended up solving the problem by taking an extra step of counting the days in between seperately. Thanks for your time :)

Kind regards
Somehow this solution doesn't work for me, but I've solved it by using an extra step. Thanks for your time:)