CODEWITHSUNDEEP
excelvbaexcel-formula
Andreas
Andreas

Reputation: 23958

Find if more rows exist based on two columns

I have the following sheet:

enter image description here

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.

Upvotes: 0

Views: 58

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

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.

Upvotes: 1

Related Questions