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

Question

Hey guys,&nbsp;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.&nbsp;&nbsp;For example&nbsp;A 1-12-2020 31-06-2021B 1-03-2021 5-05-2021A 1-04-2018 10-08-2020&nbsp;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?&nbsp;Thanks in advance!

oliverscheurich · Answer

ietsverzinnen&nbsp;=SUMPRODUCT((A2:A8="A")*(C2:C8-B2:B8))&nbsp;Maybe with this formula as shown in the attached file.

benny_1857 · Answer

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

oliverscheurich · Answer

ietsverzinnen&nbsp;=SUMPRODUCT((B:B="S4")*(I:I-E:E)&nbsp;It works in my spreadsheet if i enter&nbsp;"S4" instead of S4 in the formula.

ietsverzinnen · Answer

Hey Quadruple_Pawn,Thanks for your reply!Somehow it doesn't work for me, it gives me a value error when I use:&nbsp;=SUMPRODUCT((B:B=S4)*(I:I-E:E))&nbsp;

ietsverzinnen · Answer

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

Forum Discussion

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

6 Replies

Resources