How to count only unique values from a column in a COUNTIFS

[slam]

I have this formula so far:

=COUNTIFS(Raw!$B:$B,"11/22/2023  12:00:00 AM",Raw!S:S,A24,Raw!W:W,C23)

The problem is that my Raw worksheet has a lot of duplicates, and I only need to count one of each instance from column C on the Raw worksheet. Could someone please show me how to incorporate that into my formula?

Thanks!

 

[slam]

Hi Fluff,

Can I use this somehow in place of a SUMIFS as well? Starting with my last formula above, can it be changed from a count to a sum of Raw!Y:Y ?

Thanks!

 

[Fluff]

Yes you can do that.

 

[slam]

Yes you can do that.

I've really tired, but I'm struggling with where to fit it in. Any help would be greatly appreciated!

<3

 

[Fluff]

What have you tried?

 

[slam]

Really not sure where the SUMIFS should go, and how to change the criteria to fit, or even if I need to. Also started playing around with HSTACK, but not sure if that is needed.

Slightly different criteria for this version:

Sad Attempt 1

=IFERROR(ROWS(UNIQUE(FILTER(Raw!$C:$C,SUMIFS(Raw!$Y:$Y,(Raw!$B:$B="11/22/2023  12:00:00 AM")*(Raw!$S:$S="Planned")*(Raw!$X:$X,="2025"))))),0)

Sad Attempt 2 (there were many more! )

=IFERROR(ROWS(UNIQUE(FILTER(Raw!$C:$C,),SUMIFS(Raw!$Y:$Y,Raw!$B:$B,"11/22/2023  12:00:00 AM",Raw!$S:$S,"Planned",Raw!$X:$X,"2025"))),0)

 

[Fluff]

Why do you need to change the sumifs?

 

[slam]

Sorry, I'm not following. What do you mean by "change"?

It was working as a COUNTIFS, but now I need to use the same UNIQUE logic for a SUMIFS

 

[Fluff]

Why can't you just use a sumifs?

 

[slam]

Gotcha.

It's because I only need to SUM each value in column Y:Y one time for each instance of the ID in column C.

So the data looks like this:

Col C | Col Y
ID1 | 100
ID1 | 100
ID1 | 100
ID2 | 700
ID2 | 700

In this case, the result I am looking for would be 800 (100+700)

 

[Fluff]

In that case just change ROWS to SUM & the filter range to the column that needs to be summed.