Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Excel countifs excluding a value

Copper Contributor

Hello,  

I’m trying to count several different suppliers in a column based on the date. 

So, for example, I have 2 purchases on the 11th of September. 1st purchase has 5 items from supplier A, and each item has a row, so supplier A appears 5 times. 

On the same day, the 2nd purchase had 3 items: two items from supplier B and one item from supplier C.  

The total sales came from 3 different suppliers, but how do I count this using a formula in Excel? 

The total of items on day 11th is 8, each item has a row, and sales come from 3 suppliers. 

I want the result to appear in the column next to the data. 

So in C1, the result will be supplier A, and C2 to C5 returns blank.

5 Replies

@nessatm 

How your data is structured, is that like this?

image.png

IMG_7509.jpeg

Thanks for responding it. Column Date and Supplier are correct. Column SKU is where I need the formula, and the results should be like the screenshot. 

@Sergei Baklan 

best response confirmed by Hans Vogelaar (MVP)
Solution

@nessatm 

For such sample

image.png

in D4 is

=IF( COUNTIFS($B$4:$B4, $B4, $C$4:$C4,$C4) = 1, "Supplier " & $C4, "" )

and drag it down.

Thank you so much for your help. This is what I needed.

@nessatm , you are welcome

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@nessatm 

For such sample

image.png

in D4 is

=IF( COUNTIFS($B$4:$B4, $B4, $C$4:$C4,$C4) = 1, "Supplier " & $C4, "" )

and drag it down.

View solution in original post