Countifs not working

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
I am trying to count unique values between column H & I but my countifs is not working. I think I am not using the right formula.

I like to count the same combination from Column H & I and list the result in Column J. For example, "OSI" from Column H and "33-CE-1-10" from Column I combination should be counted only once no mater how many times the same combination appears in the data. Is there a way to do this? I don't think Countifs is the right formula to use in this case.

Any help is greatly appreciated


1616095762705.png
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows
Just thought about writing an if statement
=IF(COUNTIFS(H:H,H16, I:I,I16)>1,1,COUNTIFS(H:H,H16, I:I,I16))

I will go with the above unless there is a better way.
Thanks.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

This should also work.

Excel Formula:
=MIN(COUNTIFS(H:H,H16,I:I,I16),1)

So if the COUNT comes out 0, result is 0, if the COUNT is Greater than 1, result is 1.

EDIT: realized I forgot to remove the >1 from your formula when copied and pasted, corrected now.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Not sure if I'm missing something here, but won't you just end up with 1 in every cell in col J?
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
134
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not sure if I'm missing something here, but won't you just end up with 1 in every cell in col J?

You are absolutely correct. I realized my mistake as I was reviewing the data. Here is what I am trying to do:

Formula should look at the "Sequence" (Column C) first. If there are multiple then look at "ICO" (Column B). If they are different the count should be 2 or 3 or 4 depends on how many different ICOs are for the same sequence. (See rows 2 & 3). In other words, same combination of "Sequence" and "ICO" will get a count of 1 (rows 4 & 5)

Obviously, if there is only one unique code in "Sequence" and one unique code in "ICO" the count is 1.

I am happy to add extra (helper) columns, if need be, to go through a multistep formula.

Hopefully, I am explaining my problem a little better this time.


1616101076369.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=ROWS(UNIQUE(FILTER($B$2:$B$100,$C$2:$C$100=C2)))
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top