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

Occasional 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:)