SUMIFS while ignoring duplicate rows based on values from a column

[slam]

I think the easiest way to explain this is as follows:

I have this SUMIFS working perfectly with one workbook:

=SUMIFS(Raw!Y:Y,Raw!B:B,"11/22/2023  12:00:00 AM",Raw!X:X,"2025",Raw!S:S,"Planned")

I now have another workbook with the same layout, except that there are duplicate rows in this one. I can't remove them because they are needed for other formulas. However, I do need to ignore the duplicates in this formula. So the formula I need is exactly the same as above, but as if I'd done a Data > Remove Duplicates based on column C on the Raw worksheet.

Row 1 is the column headers, and this is for Excel 365.

Thank you!

<3

 

[AlphaFrog]

Is there a column that exists (or you could add) that would ID the duplicates and not the original? If yes, just add that criteria to your formula to exclude them .e.g column Z "<>Duplicate"

=SUMIFS(Raw!Y:Y,Raw!B:B,"11/22/2023 12:00:00 AM",Raw!X:X,"2025",Raw!S:S,"Planned", Raw!Z:Z, "<>Duplicate")

Or ID just the Original
=SUMIFS(Raw!Y:Y,Raw!B:B,"11/22/2023 12:00:00 AM",Raw!X:X,"2025",Raw!S:S,"Planned", Raw!Z:Z, "Original")

You could put a formula in column Z to ID the Original and Duplicates...
Put this in Z2 drag down column Z
=IF(COUNTIF($C$1:C2, C2)=1, "Original", "Duplicate")

 

[slam]

Hi AlphaFrog, since my original post a couple of hours ago, I tried exactly that using this formula:

=IF(COUNTIF(C$2:C2,C2)>1,"Duplicate","First")

And then I adjusted my SUMIFS to

=SUMIFS(Raw!Y:Y,Raw!B:B,"11/22/2023 12:00:00 AM",Raw!X:X,"2024",Raw!S:S,"Planned",Raw!AO:AO,"First")

However, this dataset is huge, and after adding that formula I keep getting popups about memory issues. I'm limited to 32bit Excel 365 on a laptop with 16GB of RAM, and no amount of Virtual Memory seems to solve it.

Not sure if there's a less memory intensive way to do it (or maybe I just have an issue with the workbook?). Can try copying to a brand new file, otherwise I might need a different solution.

 

[AlphaFrog]

Approximately how many rows is "huge"?

Here's another idea albeit a little kludgy. Use the CountIf formula in column AO. Then copy column AO and Paste Special Values back in Column AO to replace the Formulas.

 

[slam]

Did end up doing a few things like that to improve performance, but not quite the solution I was looking for as I need it to be more dynamic for future data dumps into this file.

120,000 rows, 35 columns, with another worksheet doing about 750 different calculations from this one, and several other smaller worksheets as well.