Counting Values Of One Column Based On The Uniqueness Of Values In Another Column

Jun 11, 2014

Here is a table

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail
9266321m34:13.0E__uMail
92677165m23:43.0E__uMail
9309415m07:24.0E__uMail

[Code] .....

Here is some context

Each one of these rows represents an entry into a ticket in our incident management system. Col4 represents the method by which the incident was reported to us (Email, Voicemail, Walk In, Ask IT). A ticket can be open and closed with a single entry if the issue is easily solved or it can have many entries if the issue is complex and requires more troubleshooting or escalation to a higher tier of support. I need to determine the number of unique tickets submitted by each reporting avenue (Email, Voicemail, Walk In, Ask IT).

In this table the number of tickets that were:

submitted via Email is 14
submitted via Voicemail is 1
submitted via Walk In is 2
submitted via Ask IT is 1

Col1 Col2 Col3 Col4
8821015m14:44.0E__uMail = 1
9266321m34:13.0E__uMail = 2
92677165m23:43.0E__uMail = 3
9309415m07:24.0E__uMail = 4

[Code] ....

What I need is a formula that will produce this result.

View 5 Replies


ADVERTISEMENT

Counting Occurrences Based On Uniqueness Of Value In Another Column

Aug 9, 2007

I want to count the number of times a certain value is found in columnB, only for unique values in columnA.

I have made a simple example file, attached. Column B shows Status which can be Active, Current, Dormant. Column A holds Item names. I want to know how many Active Items I have, but an Item can appear more than once in ColA, so I want it only to include in the count the unique ColA values.

View 9 Replies View Related

Counting Unique Values Based On Condition In Different Column?

Aug 21, 2014

Formula which will count unique values in column A based on condition in Column B which "y" .....

Show ranges as A:A instead of A1:A100 as I dont know the size of the table, it can be thousands rows .....

View 3 Replies View Related

Calculate Mean Of Column B Values Based On Date Values In Column A?

Dec 5, 2013

Below the two columns represent Column A (date and time) and Column B is temperature.

How can I take the mean of all temperature values for May 1, then take the mean of all temperature values for May 2, etc.?

5/1/2013 0:0012.4
5/1/2013 1:0012.4
5/1/2013 2:0012.5
5/1/2013 3:0012.5
5/1/2013 4:0012.5
5/1/2013 5:0012.4
5/1/2013 6:0012.4

View 2 Replies View Related

COUNTing Values In Column Two On Criteria In Column One

Feb 1, 2007

I have a workbook where I am trying to COUNT the values greater than zero in column "J" based on a selective criteria of column "B".

In other words, if the date in a column "B" cell (B3:B78) is greater than the date in A86, I want to count the number of cells whose corresponding value in the "J" column is greater than "0". And then I want to take this selective COUNT and divide it by a COUNT of all the cells in the range "J3:J78".

So, what I am trying to do is:

FORMULA /COUNT(J3:J78)

View 9 Replies View Related

Summing Values Of One Column Based On Values Of Another Column?

Feb 21, 2013

I am trying to sum certain values in one column based on the values of another column. I made a few tables, generated some random numbers, and then I started writing up an if-then statement to see if I could get what I am trying to do to work. However, I was having difficulty getting the program to run, because I was declaring more than one variable (I think).

So for instance, I want cells in column G to sum forces in column C when a joint in column E matches a joint in column A. Similarly, I am trying to do the same thing for columns F and B.

I have attached a picture, which shows what I would like for the program to do automatically (rather than me doing it all manually)! I didn't include the code I wrote, because it was so minimal

Attachment 51660

View 3 Replies View Related

Filtering Values In One Column Based On Values In Another Column

Apr 7, 2014

I'm currently working on a spreadsheet that contains 30,000 leads and their respective companies and i need to filter out any leads that work for companies present in a different list of companies (1,834 companies). Basically, i need to filter column A so that it only contains companies present in column B. Is there a way to do this using the advanced filter?

View 1 Replies View Related

Summing Values In Column Based On Values In Other Column

Nov 18, 2009

I have a vba form with three comboboxes. The first combobox contains a list of employees, the 2nd a list of work activities and the third a list containing hours. As each employee enters the data for their name, activity and hours these values are saved to sheet6 starting at A10, B10 and C10. I want to be able to sum the hours for each employee who enters data. So the completed user form could look like the following:

HTML Employee (A) Activity (B) Hours (C)
Joe Swept 1.25
Raked 1
Bill Mowed 2
Cleaned 1
Joe Trimmed 1

So as you can see there could be a blank cell in column
"A" under Joe and Bill and Joe could come back later and
enter more info. What I want on sheet6 say starting at A30 the value
Joe would be entered and in B30 his total hours 3.25. In A31
Bill would be entered and in B31 his total hours 3 and so on for other
employees

View 9 Replies View Related

Copy Values From Column A Based Upon Values In Column B

Jan 2, 2010

If values in column B are alike or if only one value exist in column B, I would like to copy values from column A over into columns D and all other columns to the right of D,

Before and after examples are below....

View 9 Replies View Related

Counting Unique Values In A Column?

Jan 17, 2008

How can I count the number of unique values in a column?

I have a spreadsheet that lists transactions that occurred over a one-year period (2007). I need to determine the number of days our facility was operational. No transactions on a particular date (25-Dec-07, for example) and the facility would be considered to have been closed on that date.

On the days that the facility was open, multiple transactions would have occurred on those open days and those multiple tranactions are all reflected in the worksheet.

So I need to count each occurrence of '01-Jan-07', '02-Jan-07', and so on.

View 9 Replies View Related

Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.

My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}

This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).

The results in the pink highlighted cells (Summary column D) should be:

Names starting with A - 3
All others - 2

I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.

View 2 Replies View Related

Counting Unique Values With Reference To Other Column?

Jun 9, 2014

I need to count all of the unique value in column C( Supplier Name) If the Owner is Analyst on column D and the Buyer is Mark column A.

I would be needing a formula to count it and not pivot table. I dont know how to set up an array and I just have learn how to use the Countif formula.

Buyer
Parts
Supplier name
Owner

[Code]......

View 3 Replies View Related

Excel 2007 :: Autofill Column B With Sequential Values Based On Value In Column A

Nov 27, 2011

Autofill column B with sequential values based on whether value in column A changes its value.

I would like to autofill column 'B' with sequential values (i.e. GenoMap1, Genomap2, Genomap3,... GenoMap10, GenoMap11, GenoMap12,... GenoMap104, GenoMap105, etc...), but changing to the sequential GenoMap# only when the value in column A changes.

This is what I imagine.

A1 "Alfiero", B1 "GenoMap1"
A2 "Alfiero", B2 "GenoMap1"
A3 "Alfiero", B3 "GenoMap1"
A4 "Allocati", B4 "GenoMap2"
A5 "Amaranto", B5 "GenoMap3"
A6 "Amaranto", B6 "GenoMap3"
A7 "Amaranto", B7 "GenoMap3"
A8 "Ambrosiano", B8 "GenoMap4"
A9 "Ambrosiano", B9 "GenoMap4"
A10 "Ambrosiano", B10 "GenoMap4"
A11 "Ambrosiano", B11 "GenoMap4"

I listed examples above of GenoMaps higher than 10 and 100 to show how I need them numbered.

I'm using MS Excel 2007 in Windows 7.

View 2 Replies View Related

Adding Values (currency) From One Column Based On Value Of Another Column (Categories)

Nov 5, 2013

I have a worksheet (Sheet1) that is constantly growing with information. I have several categories under the "Category" column and then the various amounts under "Amount" column. I would like to have Sheet2 be able to keep a running total of the "Amount" column for each category as it increases in entries. I've attached an example sheet.

Example.xlsx‎

View 1 Replies View Related

Picking Column A Values Based On Differences Between Members In Column C?

Jan 8, 2014

I have data like this,

Member
chr
location

web
1
50000

[Code]....

Now i want to select member column values based on the differences in values between members of location column should be 50000, it means if select web then my next selection should be SEB because next value (i.e web + 50000) is 100000 and it is matching to SEB in members column.

expected results
web
1
50000

[Code]....

View 2 Replies View Related

Counting Unique Values From Column Meeting Multiple Criteria?

Jun 26, 2014

I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.

I have tried different formulas, but fast running out of time

I have attached a modified sample from the main spreadsheet

I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.

View 7 Replies View Related

Sum The Values In Cells Based On The Values Other Column

Apr 25, 2006

I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:

=SUMIF(D2:D110,D112,E2:P110)

My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?

View 11 Replies View Related

Fill Column B Based On Non-unique Values In Column A?

Apr 21, 2014

I have a worksheet with Coulmn A and B , wherein , Column A has a list of values that can be duplicated. Based on the values in Column A, I want to fill corresponding cells in Column B. see the attached.

way to accomplish the same. Tried VLOOKUP.

View 1 Replies View Related

Perform Count On Column B Based On Values In Column A

May 15, 2014

I have a column that repeats it self and I want to perform a count on Column B, based on the values in Column A.

Example attached : Book2.xlsx‎

View 2 Replies View Related

Adding Values From One Column Based On Criteria In Another Column?

Jan 30, 2013

If i have the following layout of data

Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000

I would like a formula that adds all the January numbers together returning a result of 5249

To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.

I'm sure i have done this before using a countifs maybe but my mind has drawn a blank

View 1 Replies View Related

VBA To Sum Values In One Column Based On Criteria Of Another Column

May 10, 2014

The below sample is an accurate example of a much larger set of data. Column H will be my desired result.

I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.

A
B
C
D
E
F
G
H

1
Item#
Description
Quantity
Month
Year

2
1001

[Code] ........

View 9 Replies View Related

VBA - Generate Random Column Values From Existing Column Values?

Aug 22, 2013

[URL]

I have Column C and D filled with numeric temperature and humidity data. I need next 16 rows (8 more set) to be filled with randomly generated dummy data by the values exist in C and D. Same numbers but randomly distributed.

View 2 Replies View Related

Extract Largest Values From Column B And Corresponding Text Values In Column A?

Oct 1, 2013

There should be a formula to extract the largest values and its corresponding values. For example, if column

Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100

The desired outcome

Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100

The second goal is extracting the top 3 largest values using a formula.

Sample file is attached for your convenience. Desired outcome is in GREEN.

View 6 Replies View Related

Summing Up Values In Column Corresponding To Unique Distinct Values In Another Column?

Dec 24, 2013

I have two columns in excel, col A and col B.What I would want to achieve is following:

Col A Col B
A 2
A 2
A 2
B 3
B 3
B 3
B 3
C 3
C 3
C 3

Now I want a total of values in column B pertaining to unique (or say 1st occurences) of values in Col A. So in this case the output should be 8 ,Achieved as follows(2 +3+3).

[URL]

View 5 Replies View Related

Lookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D

Jan 31, 2008

I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.

I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.

View 9 Replies View Related

Find High And Low Values Of Column According To Values Of Another Column?

Jul 23, 2014

Ccheck the attached sheet for the explanation.

Book1.xlsx‎

View 2 Replies View Related

Match Values In One Column To Values In Another With A 3rd Column Included

Nov 26, 2009

I have about 360 records in Column 1 and less columns in Column 2 (333 in this case). All the values in Column 2 are in Column 1. I need to be able to match those two columns up while keeping the data in Column 3 intact with it's corresponding value in Column 2. FYI I searched and found this thread: ttp://www.excelforum.com/excel-work...n-another.html

But my problem is that I have to tie in the values with column 3 to their corresponding values in column 2. In order to illustrate, I have attached an excel file in which I did the operation manually. (note: all values in column 2 are indeed in column 1 but not in the example as I have shown for the sake of convenience).

View 2 Replies View Related

Sort Values In Column B To Match Values In Column A?

Jul 8, 2013

I have two DB extracts for 2012 and 2013 with customer id's that no not repeat across the year - columns A and B. I need to arrange this from:

2012
2013

270806
330011

330011
350529

350529
369916

to:
2012
2013

270806
0

330011
330011

350529
350529

0
369916

So the major idea is that the customers that are extinct in 2013 would appear as 0 in the Column B; customers that are the same would be aligned vs the previous year and new id's would appear on the separate row.

View 5 Replies View Related

Find / Replace From Column A Values To Column B Values VBA

Mar 6, 2014

I have a workbook with 20 sheets in English Language, but I must to translate in Russian. Translating has done by human translator, so translation is good. I must change phrases and words in english to russian. I can use find/replace function but doing by hand it's very tie consuming. I need VBA or macro who takes values in column A, finds it in entire worksheet and changes it to matching value in column B. Is it possible and how I can do it?

View 1 Replies View Related

Combining Cell Values In Column Based On Other Cell Values?

Mar 2, 2012

it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved