SOLVED

# Excel countifs excluding a value

Copper Contributor

# Excel countifs excluding a value

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

# Re: Excel countifs excluding a value

How your data is structured, is that like this?

# Re: Excel countifs excluding a value

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.

best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Excel countifs excluding a value

For such sample

in D4 is

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

and drag it down.

# Re: Excel countifs excluding a value

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

# Re: Excel countifs excluding a value

@nessatm , you are welcome

1 best response

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

# Re: Excel countifs excluding a value

For such sample

in D4 is

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

and drag it down.