Find if more rows exist based on two columns

Question

I have the following sheet:

My VBA code loops through the rows and now I need to find out if there is another line with the same column A (customer ID) and G (day of week).
Row 2596 and 2863 is both on Saturday but on different times (column H).
The formula in I3205 is sumproduct to find the time 12:05 because it looks at one row below and down only.
The issue is when there are no other duplicates as I3206 displays. The formula in that cell is from row 2864 (one line below the last one, =SUMPRODUCT((A2864:A3200=2193)*(G2864:G3200="Lördag")*H2864:H3200)) and it returns 0.
I could live with that but the issue is that 0 is also the same as 00:00, although it's not an issue now I would rather not have it return 0 but rather some error or something else that I know means no match.

What kind of other formulas can I use to get the matching time in H when it exists but also an error or something that I know for sure is not a time when there is nothing found?

The answer can either be a VBA code or a formula, I only displayed it on the sheet since that is easier than a VBA equivalent worksheetformula.

I'm not sure i understand the question. Does countifs or sumifs give you what you need? — Nathan_Sav
– Nathan_Sav, Commented Jul 19, 2021 at 10:08
If I use sumifs I still get 0 returned on =SUMIFS(H2864:H3200;A2864:A3200;2193;G2864:G3200;"Lördag"), I don't want to get 0 returned since that can be misinterpreted as the time 00:00. And countifs also give 0. — Andreas
– Andreas, Commented Jul 19, 2021 at 10:12
IF(COUNTIFS(A1:A5,"c",$B$1:$B$5,3)>0,SUMIFS($C$1:$C$5,A1:A5,"c",$B$1:$B$5,3),"None") — Nathan_Sav
– Nathan_Sav, Commented Jul 19, 2021 at 10:19
But that is just band aid. What if the time in column H is 00:00, then your formula will give me None. — Andreas
– Andreas, Commented Jul 19, 2021 at 10:21

Nathan_Sav · Accepted Answer · 2021-07-19 12:02:51Z

1

Check using COUNTIFS then use SUMIFS, like so

IF(COUNTIFS(A1:A5,"c",$B$1:$B$5,3)>0,SUMIFS($C$1:$C$5,A1:A5,"c",$B$1:$B$5,3),NA())

The #N/A error will be generated if none found.

answered Jul 19, 2021 at 12:02

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Find if more rows exist based on two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related