Formula to count unique values

uAnonimo

New Member
Joined
Dec 4, 2017
Messages
6
Hello.I've tried to Google it, but the formulas I've found are kinda of complex, I couldn't create my own version of them :(

I have a list of tasks that have indicators, I would like a formula to count all the times that an indicator appear in a task, but without counting the duplicated lines.

The formula would give me, how many G1,G2 and G3 unique tasks I have in the list.Here's the layout:
TASKSINDICATORNUMBER OF UNIQUE TASKS = G12
TASK ONEG1NUMBER OF UNIQUE TASKS = G22
TASK ONEG1NUMBER OF UNIQUE TASKS = G31
TASK ONEG1
TASK TWOG2
TASK TWOG2
TASK THREEG2
TASK THREEG2
TASK FOURG1
TASK FOURG1
TASK FIVEG3
TASK FIVEG3
TASK FIVEG3

<tbody>
</tbody>

 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ClintMc

New Member
Joined
Dec 4, 2017
Messages
4
I think the simplest way is to use a COUNTIF function.

HTML:
COUNTIF(B2:B12,"G1")
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER.
Drag formula down column as needed.
Excel Workbook
ABCDEF
1TASKSINDICATORCountG12
2TASK ONEG1G22
3TASK ONEG1G31
4TASK ONEG1
5TASK TWOG2
6TASK TWOG2
7TASK THREEG2
8TASK THREEG2
9TASK FOURG1
10TASK FOURG1
11TASK FIVEG3
12TASK FIVEG3
13TASK FIVEG3
Sheet
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

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