SumIf The Sum Of Unique Values Is Greater Than Criteria

Jul 2, 2008

I have a problem which is beyond my capabilities. I need excel to sum amount column (See example table below) by unique values in company column then conditionally sum those values based on the year of investment, stage of company and finally, the kicker, the summed Amount by unique companies value has to be less than a certain threshold.

For example, I want companies in Stage Column of Exit only and in year 2004 only BUT only if the sum of the Amounts by unique company values is less than 100. So in the example table this would return 0. I have code and criteria written to sum unique values based on similar criteria such as sum if company Stage is Seed and Year is 2004 but for the life of me I can't seem to make the jump further.

For Unique values I used this function:
=SUMPRODUCT(($B$2:$B2=Year04)*($D$2:$D2=StageS)*($A$2:$A2=$A2))=1
Then:
=DCOUNTA($A$1:$D9999,2, Criteria)
or
=DSUM($A$1:$D9999,2, Criteria)

This along with criteria explained before returns the number of unique companies that are Stage=Seed and Year=2004 or sum of amounts with that criteria.

I am trying to adapt this technique to get what I want but to this point have been unsuccessful. My best guess is that I will need to create an array of the unique companies (New Sheet column A) with the Summed Amounts (New sheet column B) and then run functions again with the easier equations and criteria? I am trying to avoid combining company records as each investment needs to remain individual and there are 16000 records. Any help would be greatly appreciated.

Company Year Amount Stage XYZ 2004 10 Seed XYZ 2004 20 Seed ABC 2004 5 Early ABC 2004 25 Early DEV 2004 14 Later DEV 2004 19 Later TRU 2004 100 Exit TRU 2004 120 Exit TRU 2004 100 Exit

View 9 Replies


ADVERTISEMENT

SUMIF Using Greater/Less Than Symbols In Text Criteria

Oct 21, 2008

I'm trying to get a simple Sumif formula to work. I have used this formula a hundred times before but this time the results are inaccurate. I suspect that it may be because of the math symbols which are used in the criteria text i.e. < and >

My formula is as follows: =SUMIF('Raw Data Export'!D:D,"< No Project >",'Raw Data Export'!G:G)

A data extract from the sheet named "Raw Data Export" is attached.

View 6 Replies View Related

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

View 9 Replies View Related

Extract Unique Values Greater Than Zero?

Oct 24, 2012

How can I extract the unique values from the original list but only those whose their SUMIFS is greater than zero? in my example "Blue" , "Yellow" & "Red" should not be extracted.

Sheet1  ABCDE1Original Unique2Purple10 Purple103Blue0 Blue04Pink1 Pink15Yellow0 Yellow06Green13 Green31
7Blue0 Red08Green18 Black29Yellow0   10Red0   11Black2   12Pink0   13Black15   14Purple6   15Red0   
Spreadsheet FormulasCellFormulaD2{
=IFERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$15),0)),"")}E2
=IF(D2<>"",SUMIFS($B$2:$B$12,$A$2:$A$12,D2),"")D3{
=IFERROR(INDEX($A$2:$A$15,MATCH(0,COUNTIF($D$1:D2,$A$2:$A$15),0)),"")}E3

[Code] ...........

View 9 Replies View Related

SUMIF - Using Multiple Criteria (10 To 20 Values)

Jun 12, 2003

Sumif formula. I need to somehow put a loop on the criteria or something...

The formula is sumif(range, criteria, sum range). The problem I am having is that I have about 20 criteria to select, and sumif is designed for one criteria. I know that I can type the exact same formaula in and have something like this...

=SUMIF(D25:D107,Variables!A6,F25:F108) + SUMIF(D25:D107,Variables!A7,F25:F108) + SUMIF(D25:D107,Variables!A8,F25:F108) + SUMIF(D25:D107,Variables!A9,F25:F108)+ SUMIF(D26:D107,Variables!A10,F26:F108)+ SUMIF(D26:D107,Variables!A11,F26:F108)

This is OK for a few variables but some formulas have multiple criteria in excess of 15-20. the other thing is that the criteria list will grow over time as more variable are added, and instead of changing the formulas throughout the spreadsheet, I would like to have a big range that I can slowly fill up as I go along

The ideal situation would be if I could put a range in ( ie A6:A30) and then when I get a new criteria I just add it to this range. This will make administration eaaseier by giving me one location to update data.

I will show you an example.. I want to calculate the amount of petrol I use based on three criteria - BP, Shell, Caltex ( the criteria being petrol stations that come up on my bill).

Column
A/ B / C/ D
Row / description / Amount/Variables
1 / Shell Petrol / $10/Shell
2 / Cat Food/$13/BP
3 / BP Store/$24 / Caltex
4 / Dog Food/$23
5/Shell Petrol / $98
6/Caltex Petrol/$31
7/BP stuff/$30
8/Shell Store/$70

What I have been doing is this...

=sumif(B1:B8,D1,c1:c8) + sumif(B1:B8,D2,c1:c8) + sumif(B1:B8,D3,c1:c8)

If I could somehow get the formula to choose the whole column D as the criteria range and get the 'sum if' to somehow loop so it goes down the d column and does the same calculation, but just changes the criteria variable each time, then instead of changing the formula, I could just add to the D column each time I had a new criteria I wanted to check.

I looked into DSUM and Pivot Tables. DSUM seems to work well with numbers, not variables and Pivot tables did my head in, especially seeing I know that there is a way to do this in a formula based way..

View 9 Replies View Related

Multiple Criteria And SUMPRODUCT (count The Number Of Rows That Have Values Greater Than 10/01/2008 In Either Of Two Fields)

Jan 23, 2009

I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.

View 2 Replies View Related

Sum Unique Values Under Criteria

Apr 16, 2009

With reference to a thread that is nearly similar to my own problem:

http://www.excelforum.com/excel-gene...ml#post2076546

I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the C-column. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"".

Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.

View 6 Replies View Related

Unique Values Based On Criteria

Jun 20, 2013

I've got an interesting problem which I haven't been able to find an answer for.

A B
PR Yes
PR Yes
PR Yes
DN Yes
DN No
DN No

I need to count how many unique values in column a have every row in column b the same value. So counting for yes values, PR would equal one but DN would equal zero.

I used this formula to find out how many unique files had yes values, now I need to find out how many files only have yes next to them.

=SUMPRODUCT(($B$2:$B$14="Yes")/COUNTIFS($B$2:$B$14,$B$2:$B$14&"",$A$2:$A$14,$A$2:$A$14&""))

View 8 Replies View Related

Counting Unique Values With Multiple Criteria?

Aug 13, 2014

I am looking to count the unique amount of customers who are listed in column C based on the criteria that they purchased the product on the 1/08/2014 and that the product came from Department 3. The output I am looking for in this example would be 3. I can do it for this example but when I have 300 different customers it starts getting tricky and I'm a bit stumped on how to incorporate an array formula into a countifs() function or whether there is an alternative.

Date DepartmentCustomer
1/08/2014 3 A
1/08/2014 3 B
1/08/2014 2 C
1/08/2014 3 D
4/08/2014 3 A
5/08/2014 2 A
5/08/2014 3 D

View 7 Replies View Related

Counting Unique Values With Multiple Criteria?

Feb 25, 2009

I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:

2366 2005 T [number of corresponding unique values of B]

For example:

--D---C---A-B
2366 2005 T 2
(as opposed to 3)

A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372

View 9 Replies View Related

Count Unique Values With Multiple Criteria

Oct 27, 2011

I have this formula to give me the number of unique locations and it works fine for cell J1:

=SUM(IF(FREQUENCY(MATCH($B$3:$B$22,$B$3:$B$22,0),MATCH($B$3:$B$22,$B$3:$B$22,0))>0,1))

What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.

View 2 Replies View Related

Count Unique Values With Specific Criteria

Oct 31, 2011

I have data in the following type

A Criteria B
A Criteria C Criteria A
C Criteria C
D Criteria 1
2 Criteria 3

Criteria 2
3 Criteria

I need a formula that will count the number of unique values in column A that have "Criteria" in column B. In this case the desired output would be 5 (A, C, D, 2, and 3), a blank cell in column A does not need to be counted.

View 9 Replies View Related

Counting Unique Values Based On 2 Criteria

Aug 11, 2007

Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............

I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.

View 4 Replies View Related

Count/Sum Unique Values Based On Criteria

Feb 19, 2009

I'm facing a big problem and I can't found a solution for days.

I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:

=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

Let me Put to you an example for you been able to understand my problem.

Example considering Range "C2:C20" exluding blank cells

(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))

(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))

(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))

They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....

View 9 Replies View Related

Counting Unique Values With Multiple Criteria

Feb 26, 2009

I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:

2366 2005 T [number of corresponding unique values of B]

For example:

--D---C---A-B
2366 2005 T 2
(as opposed to 3)

A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372

View 9 Replies View Related

Count Unique Values With Specific Criteria

Dec 3, 2007

I need to count the amount of Unique codes in column A BUT ONLY if they are still OPEN status. Hopefully the attached example will be explaination enough (I want to automate the RED figure...

View 3 Replies View Related

SUMIF: Value Is Greater Than Zero

Oct 20, 2009

I have this formula to tell me how many times in a given column the value is greater than zero:

=COUNTIF('Data Entry'!AY3:AY117, ">0")

But I also need to know how many times the value in AY3:AY117 is greater than zero PLUS how many times the value in AZ3:AZ117 is also greater than zero

I've scoured this wonderful website and have got as far as:

=SUM(IF('Data Entry'!AY3:AY117,"> 0"+SUM(IF(AZ3:AZ117,"> 0"))))

but I'm clearly still way off.

View 9 Replies View Related

Create Unique List Of Values From Two Ranges With Criteria?

Jun 12, 2014

I have two lists of values in separate columns with condition in adjacent cells, all on the same sheet

List 1 consists of numbers from 1 to 12, column to the right is either blank or Free
List 2 consists of numbers from 13 to 24, column to the right is either blank of Free

List 1 starts in A2, B2 is either blank or Free
List 2 starts in D2, E2 is either blank or Free

In column G, from G2 i want it to list all values from List 1 and List 2 where their status is Free

View 14 Replies View Related

Counting Unique / Different Values Based On Multiple Criteria?

Jan 21, 2014

I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).

Program
Clinic Title
Start Date

[Code].....

formula that will automatically give me that answer?

View 2 Replies View Related

Count Unique Values Based On Matching Criteria

Jan 9, 2007

I have a worksheet with two tabs.

First Tab
Account Name
Account Number

Second Tab
Account Name
Account Number
Account Ship Location Number

On the first sheet each account name and number only appears once.

On the second sheet there may be multiple entries for each Account Name and Number.

On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.

View 9 Replies View Related

Loop Criteria Based On Unique Values In A Column

Apr 3, 2009

i had a database from which i need to extract data from COlUMN B, select the

View 2 Replies View Related

Match With Multiple Criteria To Dataset Of Unique Values?

Jul 8, 2013

In one sheet I'd like the user to select from 3 dropdown lists certain predefined values.

On the second sheet there is a long list of unique cells (one column, that can't be split into usefull columns with text to columns or something). I want to find the cell that holds the three text choices. These can be in different order to make things more complex. How do I create a search that finds that one match.

Example
The user selects "AAA" and "DDD" and "FFF" from the dropdown lists

The formula should find that one cell that holds this value: "FFF JJJ GGG DDD CCC AAA". This is the only cell that holds all three chosen values in one text. In the end I would need to have the row number of that cell,

View 7 Replies View Related

Sumif Formula - Greater Or Less Than

Feb 23, 2014

Below is my formula that works for a specific date. F$8 is the date.

How could I change this if I want data b/w two time periods.

>=F$6, and <=F$8?

[Code] ....

View 3 Replies View Related

Count Unique Values With Multiple Criteria And Using Wild Cards?

Oct 18, 2012

I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).

View 6 Replies View Related

Count Unique Values If Adjacent Cell Meets Criteria

Apr 30, 2014

In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.

Capture.PNG

At the moment I have a formula as below:

=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))

this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.

Modifying my formula. I have attached the sample workbook below.

Book1.xlsx

View 7 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

Formula That Counts Number Of Unique Text Values If Meets Criteria

Nov 6, 2013

I have been using this function to count the number of unique text values in a data set:

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).

View 7 Replies View Related

Function / Formula In Excel To Count Unique Values Matching Criteria

Jun 4, 2013

I have a worksheet. I would like to count unique number of "Trans" in column A only if value of cells in column B "Type" equals "Return". In example below, I would want to see the value "3" as total.

Trans Type
1 Return
2 Return
2 Return
3 Exch
4 Exch
5 Return
5 Return

View 9 Replies View Related

SUMIF Function: Sum A Range Of Cells Greater Than Whatever Is In Cell D8

Jul 18, 2006

I am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)

The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.

View 2 Replies View Related

Sumif Unique

Jul 14, 2006

I would like to sum up but ingore duplicates but there is one criteria.

This what I got so far but no luck.

=SUM(IF($A$3:$A$40="SINTO (C&D #4)",IF(ISNA(MATCH($B$3:$B$40,,0)),1/COUNTIF($B$3:$B$40,$B$3:$B$40))))

Example workbook below.

View 9 Replies View Related







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