SUBTOTAL IF Formula To Sum Distinct Values In A Column
Jan 2, 2010
I'm looking for a SUBTOTAl IF function (if there's any) that would sum all the distinct values in a column for a given criteria based on another column. My data is listed below:
Column A Column B
(Quarter) (Revenue)
Q1 2008 $10
Q1 2008 $10
Q1 2008 $20
Q2 2008 $15
Q3 2008 $25
Q3 2008 $25
I'm looking for a formula to sum the DISTINCT Revenue values for Q1 2008, Q2 2008, and Q3 2008 based on the autofiltered list. The formula result should change dynamically based on the autofilter selection.
View 9 Replies
ADVERTISEMENT
Oct 23, 2008
I’m trying to get my sheet so that at each change in month it creates a sum of the value but I want to sum to show up in the subtotal value column.....
View 10 Replies
View Related
Mar 5, 2014
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
View 2 Replies
View Related
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
Jan 21, 2014
I have a spreadsheet which contains (see illustration below):
1. a column called LEAD ID which contains a unique id
2. a column called WON which signifies if the deal was won/not won (1 = WON, blank = Not Won)
I can have multiples of the same value in the Won and Unique Id field (see illustration below)
I would like to create a formula field which returns the summary of the No. of Deals WON
I need to tie the formula back to the Lead ID and whether or not the WON field contains 1 or not.
I've tried this formula but it won't work
(Lead ID is in Column A, WON is in column B)
=IFERROR(SUM(1/COUNTIF((A:A,A:A) AND(B2=1)),"0")
LEAD ID WON No. Won (Formula field)
abc112 0
abc222 1 .25
abc222 1 .25
abc222 1 .25
abc222 1 .25
abc333 1 1
abc444 0
View 9 Replies
View Related
Dec 23, 2006
I have this formula that works perfectly in Excel. It counts the unique TEXT values in a column. But it does not work in the VBA editor. Count = SUMPRODUCT((C6:C39<>"")/COUNTIF(C6:C39,C6:C39&""))
View 2 Replies
View Related
Nov 26, 2012
I have a table with 200.000 rows.
I need to create a helper column, that marks distinct values with 1 and not distinct "" or 0.
I tried the following but both use countif and I believe it is the problem it's just to slow.
=IF(COUNTIF($A$2:A2;A2);1;0)
=1/COUNTIF($A$2:A2;A2)
but both are really slow +20 mins to calculate and many times Excel crashes( 2007 32bit).
The workbook is really simple
Col1 (number formatted as text) - Col2 =distinct
View 6 Replies
View Related
Jan 11, 2013
The code below extracts unique distinct values from column A, except cell A1. Then the code adds the extracted unique distinct values to the combo box.
Sub FilterUniqueData()
Dim Lrow As Long, test As New Collection
Dim Value As Variant, temp() As Variant
ReDim temp(0)
On Error Resume Next
With Worksheets("Sheet1")
[Code]....
The vba code below copies the selected value to cell C5 whenever the combobox is selected
Copy the code into a standard module.Assign this macro to the combobox
Sub SelectedValue()
With Worksheets("Sheet1").Shapes("Drop Down 1").ControlFormat Worksheets("Sheet1").Range("C5") = .List(.Value)
End With
End Sub
Vba code belows instanly refresh the combobox when values are added/edited or removed from column A,
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("$A:$A")) Is Nothing Then
Call FilterUniqueData
End If
End Sub
My problem is this:
Everything will be working fine if the all combobox and data can be found in 1 sheet, but how could i make some tweaks if the combo box is located in sheet 2 provided your data to be populated is in sheet 1, i tried everything but the combobox doesnt update,
View 1 Replies
View Related
May 9, 2014
I have a data set of students results and I have been using countif to pickup similar grades eg, C1, C2 and C3 all as C. However I need to subtotal filter against teachers and the sumproduct won't let me use C* as a wildcard to pick up the three types of C. How I can subtoal countif multiple criteria.
View 3 Replies
View Related
Jul 30, 2014
I have a set of cells and i want it to return all of the distinct or unique values within that matrix. for example, lets say this table charts how many sales an employee makes in a week.
sun mon tues weds thurs fri sat
john doe 1 0 3 2 3 0 0
jane doe 0 8 0 1 0 7 0
james doe 3 3 2 3 2 1 1
the desired output would be: 0,1,2,3,7,8
I don't want to be copying all of the cells or doing a lot of manipulation - i know i can copy, sort, and get unique values that way - i want a formula where as i plug in values, this field is updated to include all of the distinct or unique values.
View 2 Replies
View Related
Sep 18, 2012
I'm a huge fan of the DistinctValues function published by C Pearson here: Distinct Values Function
Now I have the problem that I need to get all the distinct values from a 2-dimensional range.
View 1 Replies
View Related
Apr 18, 2013
I am trying to count multiple occurences.
Instrument Name
Times Traded
AEON CO LTD
6
ASICS CORP
3
ELECTRIC POWER DEVELOPMENT C
10
In column C (Times Traded) I have used a simple countif formula: =COUNTIF($B$2:$B$8068, C2)
Now I have to check for how many times this instrument was bought or sold. This is in column A .... How will I count the Buy values ... cause this formula I devised doesnt work =COUNTIF($B$2:$B$8068, and(C2, A2:A8068="Buy")
View 4 Replies
View Related
Feb 24, 2014
I have an excel which is containing records of users who have given an online test.Many users have given the test multiple times. I need to extract the records of every individual with the highest score. example it could be individual A, B and C have given the test 3 times with different scores achieved. I need the records of A, B and C in which their score was maximum.
View 7 Replies
View Related
Apr 7, 2008
I have a workbook with two worksheets:
Input
Entitlment
On workbook called "Input" range A2 downwards are a series of staff names, non unique
When I select the worksheet called "Entitlement" I would like on this sheet range A2 downwards to list staff names in alphabetical order but only unique occurances of a persons name.
eg if on sheet "Input" cell A2="darrenS", A3="darrenS", A4="EdwardG", A5="EdwardH"
upon selecting "Entitlement" sheet
A2 would show "darrenS"
A3 would show "EdwardG"
A4 would show "EdwardH"
I user will then have to enter a number relating to number of accounts in ColumnB
should a name already have an existing number next to it then when the worksheet "Entitlment" is selected again the number will reamin associated to the name. When sorting occurs !
View 9 Replies
View Related
Mar 15, 2012
I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell
{=INDEX(List,MATCH(0,COUNTIF($b$1:b1,List),0))}
View 5 Replies
View Related
Oct 8, 2009
i am looking to create a small table from the attached worksheet that will show how many users there are for 3 different locations, the user names are in column a and the locations in column c. the thing is, the actual worksheet i am using in work contains almost 1000 lines, and is being updated daily, there can be multiple entries for a person for projects etc.. and someone can even be in the availability and pto area's without being in the main project area. each resource can only be on one location so that will not change.
also, there can be blank cells for resource which should not be counted. i was wondering if there was a formula or a macro that would count the distinct number of names and reference them to the location and give a count of resources by location?
View 2 Replies
View Related
Jan 28, 2010
I want to count the number of unique, or distinct, company names in column [C7], subject to two conditions that will exclude certain unique company names from the count.
I should also point out that most company names appear multiple times in column [C7].
a) Count the unique company names in column [C7]...
b) ...including only those companies who have at least one "Yes" in column [C8] somewhere among their records
c) ...and who also have at least one value equal or greater than "1" in column [C15] somewhere among their records
Note that that there is no requirement that the "Yes" and the "1" ever appear in the same record.
View 2 Replies
View Related
May 30, 2014
I have two lists in different columns, which are defined ranges. I want to extract them to a unique list with an array formula and at the same time sort albafetically, without duplicates, like this:
List1
List2
Final List
Audi
Ford
[Code] ....
So far, I have this formula which is working in what concerns removing duplicates and exctract a unique list. But it is not ordering alfabeticaly.
{=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($D$1:D1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($D$1:D1, List2), 0))), "")}
View 2 Replies
View Related
Aug 22, 2012
Someone has sent me a spreadsheet and there are subtotals on it. I want to remove the subtotals add loads of new lines and then subtotal it again rather than trying to scroll though each line and add stuff. However I'm not sure how the subtotal is set up is there an easy way to identify what the subtotal consists of?
View 1 Replies
View Related
May 29, 2007
I have a large list of data including subtotals. I want to copy and paste just the values of the subtotals to another worksheet. I have tried collapsing the list to Level 2 and copying/pasting values but this still carries over the whole list.
View 3 Replies
View Related
Feb 5, 2009
I have data in a pivot table that has some positive and negative values. At the end of the Pivot Table, it sums the values for each month and then a grand total.
I would like to also have it show the summation of all negative values per month. So it would be:Jan Total
Jan Negative Total
Feb Total
Feb Negative Total
etc...
Grand Total
Grand Negative Total
Is it possible to add such functionality?
Image for reference:
View 9 Replies
View Related
Jul 12, 2006
Is it possible to use the worksheet function Vlookup on subtotal values? If so what is the formula? The problem that I am trying to solve is as follows. I have a sheet (1) listing part numbers that are not moving at a particular depot. On another sheet (2) there are details of sales relating to the non-moving part numbers at 6 other depots, I have added subtotals. Currently I am switching between worksheets to see if I can transfer stock. I would like to id on sheet 1 the total number of sales at other depots, then I can see what part numbers may be transferrable without a great deal of manual intervention.
View 2 Replies
View Related
Feb 6, 2013
I have a workbook with two tabs, Summary & January. The january tab has total expenses for the month of january that are subtotaled by last name. i.e. (baker total, decker total..). I would like to be able to use a formula on the summary tab to pull the values of each of the totals for each person from the january tab to the summary tab.
I tried on the summary sheet doing =jan and selecting the first persons name and then going back to the summary tab and dragging that formula down but it pulls all of the other data within the subtotal on the jan tab and I just want each persons total.
View 5 Replies
View Related
Feb 20, 2014
I have a sheet with about 3000 lines that looks something like this:
A...............B..................C
1234........$42.................Subtotal of all values in group "1234"
1839........$58.................Subtotal of all values in group "1839"
1837........$15.................etc
1234........$11.................etc
1592........$19.................etc
The numbers in column A refer to a specific "group" number - anything in this group I want to subtotal into column C.
I'm not sure how to do this though. I've looked at the =SUBTOTAL function, but I'm not sure exactly what to do to make what I'm trying to do work.
I wrote a For Each loop to go through each cell and add values, but that took like 4 hours to run - so.. that's not the way to go lol.
View 4 Replies
View Related
Feb 23, 2012
I have a problem Im wanting to subtotal a range of filtered totals based on column A. Basically skip any filtered total where column A is blank
Column A Column B
735512172626
So when using subtotal 109 it will skip 5 as there is no figure in column A
I am currently using the below code to create the subtotal.
LR& = Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row
With Range("H" & LR& + 2 & "," & "I" & LR& + 2 & "").Offset(3, 0)
.FormulaR1C1 = "=SUBTOTAL(109, R[-" & LR& & "]C:R[-1]C)"
View 6 Replies
View Related
May 29, 2006
This question is probably simple for some of you but I can't seem to figure it out. All I am doing is grouping data by company name in column A and taking the value of orders in column P and subtotaling them. What I would like to do is offset the subtotal to be in column Q instead of totaling in column P. This is my code for the subtotal:
Sub CustomerWarrantySubtotals()
Dim EOF As Integer
Sheets("Customer Warranty").Activate
EOF = Columns("A:A"). find(what:="", lookat:=xlWhole).Row - 1
Range("A1:P" & EOF).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(16), _
Replace:=False, PageBreaks:=False, summaryBelowData:=True
'Is there anything in here that will let me change what column to put the total in?
End Sub
View 6 Replies
View Related
Feb 5, 2007
way to do this but i have a sheet that is into 5 - 6 thous rows, in one of the columns (names) i sort it by names and then order it by subtotal for certain values.
What i need to know, is there anyway i can take just the subtotal values out and put onto another spreadsheet without copying and pasting it all as there are lots of subtotals and this would help alot as the other info is not nec. just the subtotal'd info. either that or is there anyway i can highlight the subtotal'd row info in yellow/bold text anything like that that would make it stand out without having to do it manually?
View 12 Replies
View Related
Apr 30, 2009
I am desperately trying to add a simple subtotal formula but receive the same error ("Type mismatch"):
View 4 Replies
View Related
Aug 21, 2007
The following is what I have in A1:C3.
4 TRUE 0
4 FALSE 4
4 FALSE 4
The Formula in C1 is =IF(B1=TRUE,SUBTOTAL(9,C2:C3),A1)
This formula is then copied down to C2 and C3. I want this formula to subtotal the next two numbers if the cell to the left is true. Right now it is giving me 0, but it should be giving me 8.
View 9 Replies
View Related
Jun 12, 2009
I'd like to point a cell to a subtotal on another sheet but that sheet will periodically have the subtotals removed, more data added, then sorted and subtotalled again. Is there a way to have my other cell find the subtotal that matches a certain criterion (in this case, cost center)?
View 9 Replies
View Related
