Excel Formula to count unique values based on criteria

[penfold]

I am trying to use a formula to get the count of computers based on a value in another cell. What I am trying to do is get the number of computers with specific software listen in another column. In order to do this I have a summary tab which provides feedback with specific counts but I want to add an extra column and I am trying to use the following formula

 

=SUM(IF(A4=Sheet1!A1:A10, 1/(COUNTIFS(Sheet1!A1:A10, A4, Sheet1!C1:C10, Sheet1!C1:C10)), 0))

 

To explain - A4 is the data I want to filter on so anytime A4 is listed I want to count the unique number of computers that has this. Now if I use the formula above I can get the formula to work. But as I am trying to create a template I don't want to specify the numbers in Sheet 1 as this will change. I have tried changing Sheet1!A1:A10 to Sheet1!A:A but if I do that then it throws an error and just outputs 0 as the result.

 

I am basing my formula on this page - https://www.get-digital-help.com/count-unique-distinct-values-based-on-a-condition/

 

I'm obviously doing something wrong, but can't see what. If there is an easier way to get the unique count of computers based on a value in another column I would really appreciate that.

 

Anyone have any ideas what I'm doing wrong?

[Scan099]

Can you post an example of the spreadsheet please?

[Ditto]

Agreed, a sample would help. Later versions of Excel (including 365 I believe) brought in a UNIQUE function - it might simplify things. I also find Google sheets better at handling Unique counts in case that option is available to you.

[penfold]

Actually, I'm using this to count unique entries which works for total number of computers. But what I need to do is have this count but I want to filter the count based on the value. I could run another report to generate this information, but currently I'm trying to see if I can calculate it from the existing report as I'd like it in 1 place. Let me see if I can post an example

 

Explanation: Sheet 1 which has raw data on computers and software on them. The columns I'm interested in is Sheet1 A which contains software and Sheet1 C which contains computer Name

 

I have a summary tab which calculates the total no of computers using the formula =COUNTA(UNIQUE(Sheet1!C:C))

 

Also I want to be able to add a breakdown of computers so basically something like this.

Summary Tab

[TABLE=width: 500]

[TR]

[TD]A[/TD]

[TD]B[/TD]

[TD]Formula[/TD]

[/TR]

[TR]

[TD]Total[/TD]

[TD]100[/TD]

[TD]=COUNTA(UNIQUE(Sheet1!C:C))[/TD]

[/TR]

[TR]

[TD]Word[/TD]

[TD]13[/TD]

[TD]?[/TD]

[/TR]

[TR]

[TD]Excel[/TD]

[TD]10[/TD]

[TD]?[/TD]

[/TR]

[TR]

[TD][/TD]

[TD][/TD]

[TD][/TD]

[/TR]

[/TABLE]

 

What I'm trying to do is count the unique entries in column C from Sheet 1 when Sheet1 column A contains the value in A2. Where the number of computers with Word & Excel would be 13 and 10 respectively.

Edited March 9, 2021 by penfold

[SpreadsheetJockey]

Can you create a column with a CONCAT formula (concatenating A & C), and then use your UNIQUE formula on that column?

[Scan099]

hi, if you could post a dummy spreadsheet that would help but i think you need a countifs statement which will allow you to put critiea in, for example

 

COUNTIFS(Stock!$F$2:$F$986,"8300 CMT",Stock!$J$2:$J$986,"unBuilt")

 

it is looking in the stock tab column f for "CMT" then looking in column J for "unbuilt" and only counts if CMT and unbuilt are matched.

[Ditto]

I can't be certain without a working sample, but I suspect if you put your data in to an Excel table (from the menu File->Insert depending on version) and then make use of a Filter or Slicer, it might provide you with a solution.

https://support.microsoft.com/en-us/office/filter-data-in-a-range-or-table-01832226-31b5-4568-8806-38c37dcc180e

https://support.microsoft.com/en-us/office/use-slicers-to-filter-data-249f966b-a9d5-4b0f-b31a-12651785d29d

[clareq]

Would a pivot table get the data you're looking for?

[penfold]

I ended up using this formula =COUNTA(UNIQUE(FILTER(Sheet1!$C$1:$C$124298,Sheet1!$Q$1:$Q$124298=A2))) which works. My issue was that I didn't want to use C$1:$C$124298 as I didn't want to put a fixed number into the formula. However, for what I needed it was quicker to do this and just check that it covers the number of rows rather than continue with troubleshooting the formula.

 

The things with using a table is it seems to offer the same thing - I need to select the range I am using. All I was trying to achieve was to create a template tab and then update the data in Sheet1 each time I run a report so the other tabs would auto update. This is possible in some of the other formula I have used by using Sheet1!A:A. It seems I can't do this for the formula I ended up using. There is already a little bit of manual effort here so I was trying to avoid it

 

Thanks all