# Counting One Range With Conditions In Another Range

Aug 17, 2009
I have random "X"s in a column of cells in a range called "Won". I wish to count these if the adjacent cell in another range alled "Valu" is greater than a value determined in the cell E3. The result to be entered in Cell G4. Can use Excel formulas or VBA.

View 4 Replies
ADVERTISEMENT
Aug 16, 2009

I have a named range “Value” and wish to count all the values over the value set in cell E3. The result to be entered in cell F4.

I have a second named range “Won”, adjacent to “Value”, and wish to count these values if the adjacent cell in the “Value” range has been counted. The result to be entered in cell G4.

View 2 Replies
View Related
May 15, 2012

How to get the MAX of a range if 3 conditions are met.

1 after this date

2 before this date

3 Column G is equal to A2

Ranges are on sheet1

Date reference is on sheet2

range to sum is on sheet1

V14.

something like

MAX sheet1!H:H

IF sheet1!$G:$G equals $A2

AND sheet1!$P:$P,">="&EOMONTH($B2,-1)+1

AND sheet1!$P:$P,"

View 7 Replies
View Related
May 15, 2012

How to get the sum of a range if 3 conditions are met.

1 after this date

2 before this date

3 Column G is "OTH"

Ranges are on sheet1

Date reference is on sheet2

range to sum is on sheet1

V14.

Present formula is:

=SUMIF('sheet1'!$P:$P,">="&EOMONTH($A2, -1)+1,'sheet1'!$H:$H)

-SUMIF('sheet1'!$P:$P,">"&EOMONTH($A2,0),'sheet1'!$H:$H)

This works fine but lacks the 3rd condition 'sheet1'!$G:$G ="OTH"

Advise given was to go on SUMPRODUCT but unless with (OFFSET(INDIRECT, SUMPRODUCT wouldn't work on multiple sheets. And this is for version 14...

View 5 Replies
View Related
Aug 10, 2009

I need a macro that will delete a range with some certain conditions. I have a set of dates in column B. The dates are in the format of 2009-01-31 09:15:00.

I need to search this column to find all the dates from the day before the computers date to the first day of the month before the previous days date. For example my computer date right now is August 10, 2009. So i need the macro to find all the dates from August 09, 2009 back to July 01, 2009 and select the entire rows that meet this criteria. Then I need to delete those rows.

Tomorrow when I run the macro it would choose all the data from July 01 - Aug 10, and this will go on each day until the end of december.

View 9 Replies
View Related
Dec 6, 2007

I have written a function below which is supposed to give average of selected range and will ignore #N/A, if any. Naturally, i use array formula:

Function m_avg(mRange As Range)

m_avg.FormulaArray = "=AVERAGE(IF(ISNUMBER(mRange),mRange))"

End Function

the above doesnt work and gives me #VALUE!.

View 3 Replies
View Related
Jul 27, 2012

I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.

First condition:

1 2 3 4 5 6 7 8 9 10

The function would return: 1+2+3+4+5 = 15

Second condition:

0 0 0 4 5 6 7 8 9 10

Function would return: 4 + 5 + 6 + 7 + 8 = 30

Third condition:

0 0 0 0 0 0 0 8 9 10

The function would return: 10 + 9 + 8 = 27

I have had success with:

=B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5)))

(using ctrl shift enter)

BUT it crashes when faced with the third condition.

I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.

View 3 Replies
View Related
May 14, 2014

1) Input data are static and helper columns can be added if needed.

2) Filter will be dynamic range (in attachement is the filter static), and the count of years can be changed on users request. So there could be only 2011, but also 2011+2012, 2012+2013+2014 etc.

3) In col 'J', the is what I know to do, but I do not want to use SUMIF+SUMIF+SUMIF... for each year (the count of years will change througt time as mentioned above).

View 3 Replies
View Related
Mar 15, 2014

I am trying to pick certain data from a table and sum it in order to produce a formatted report.

The data has many different items of which I want to pick certain ones to sum on one line

See attached and some of the formula's that I have used, but aren't quite what I'm after

The formula I want to use is .... VLOOKUP(E5:G5,A14:C23,3,) ......

But it wont reference a range (E5:G5) !!! and I also want the formula to ignore blanks (as blanks in the data file are actual totals).

View 2 Replies
View Related
Jul 31, 2008

My data is as follows

MonthMilesAmountLocation

June 8£3.20 Loc1

June 8£3.20 Loc1

June 8£3.20 Loc2

July 8£3.20 Loc2

July 8£3.20 Loc1

July 38£15.20Loc1

July 4£1.60 Loc2

Aug 38£15.20Loc2

Aug 4£1.60 Loc1

Aug 8£3.20 Loc1

Here is what I want to do

- Avoide filtering and manually calculating data for the total expense in the month

I want to SUM all the (3)AMOUNT in the (1)MONTH of JUNE for

(4)LOCATION LOC1

I know its too much to ask for but can you suggest if i can combine SUMIF or SUM(IF) or any other formulas ???

View 10 Replies
View Related
Dec 17, 2012

I'm trying to code a clean up of some data ranges ready for priting / reporting. I need to move filtered data from the source sheet, to a new one.

The issue is, that I need to apply some logic to the rows I filter out, not something I can do with autofilter...

- Only copy rows including and BETWEEN the first and last rows where total count in col 8 is > 0

So in example1, I'd want to copy out rows P6 - 1A

BUT

- Always copy at least 5 rows when there is one value > 0 in col 8

So in example2, I'd want to copy out rows P5 - 1C, ideally...

Total

Boys

Girls

Total

[Code]....

View 3 Replies
View Related
Nov 13, 2006

I'm creating a summary sheet that adds up all the data on sheets between start and finish where A2-A25 is SU and B2-B25 is 004*04. Background info: SU stands for SetUp, and 004*04 is a workcenter number (its not 004 times 04) I'm trying to calculate the total number of Setups for each workcenter.

=SUM((start:finish!A2:A25="SU")*(start:finish!B2:B25="004*04"))

gives me a #NAME? error.

Example Data on each spreadsheet:

A B

SU 004*02

SU 004*04

SU 004*04

SU 004*02

SU 004*04

View 9 Replies
View Related
Dec 7, 2006

In the screen shot I'm trying to find the row number where a particular price of an order has been reached. In this case, for the first order, my execution price is 1.8859, my stop loss is 1.8834 and take profit is 1.8884. I need to look and the future prices to determine which event had occured first (either the take profit or the stop loss). I though by using row numbers I would compare and which ever is smallest would mean that it occured first - the profit/loss is then calculated.

The other caveat is that an exact match may not always be available - for example, the second trade is stoped out because the highest price for the 12:35 timeframe exceeds the value I'm looking for. Still it would have triggered a stop loss.

******** ******************** ************************************************************************>Microsoft Excel - Misc.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH6I6J6M6H7I7J7M7H8I8J8M8I9J9M9I10J10M10I11J11M11I12J12M12=

ABCDEFGHIJKLM3DateTimeOpenHighLow*Order*PlacedOrder*PriceStop*LossTake*ProfitStop*Loss*Row*#Take*Profit*Row*#Profit/Loss42006.11.1512:001.88651.88661.8863*N***these*are*the*cells*that*need*the*formula*52006.11.1512:051.88651.88661.8856*N******62006.11.1512:101.88591.88591.8857*Buy1.88591.88341.88841080.002572006.11.1512:151.88581.88591.8853*N*...............

View 10 Replies
View Related
Sep 5, 2006

I have this macro to copy a range to another worksheet:

Sub transpose_UPCID() ' Hace el copy y paste transposed en BAUCS de los numeros de UPC"s y

' las cajas ATR

Dim rng1 As Range

Dim rng2 As Range

Set rng1 = Sheets("Sales-Inventory").Range("UPC", Range("MK_ID").End(xlDown))

Set rng2 = Sheets("Sales-Inventory").Range("Figure", Range("Figure").End(xlDown))

rng1.Copy

Sheets("BAUCS").Range("C11").PasteSpecial Paste:=xlValues, _

Operation:=xlNone, SkipBlanks:=False _

, Transpose:=True

rng2.Copy

Sheets("BAUCS").Range("C37").PasteSpecial Paste:=xlValues, _

Operation:=xlNone, SkipBlanks:=False _

, Transpose:=True

Application.CutCopyMode = True

End Sub

The only problem is that this code copies all the items and I need this code to look into Column A in the Sales-Inventory worksheet. There I have a data validation with a total of six options. I also have six differents worksheets where I need to copy the values based on that data validation. To give you and example, all items containing XX in column A copy to XX worksheet. Something like that.

View 9 Replies
View Related
May 16, 2007

Please take a look at this sample spreadsheet.

[url]

I am trying to find out how to write a formula that will sum the values in one column if the value in a corresponding column is within a range. For example, if the item number in the first column is greater than 2 and less than 7, the sum would be 179. I believe this would be some type of SUMIF but I cannot write it.

View 3 Replies
View Related
Dec 7, 2007

I have a VERY complex custom data validation formula that is getting crazy. I thought it might be easier to use a user defined VBA function to handle it, but was unsuccessful.

I was able to write a VBA User Defined Function & use it within my spreadsheet to derive the value of another cell, as follows: =IF(OR(ISBLANK(B12),myValidation(B12)),"OK","NG")

I tried using it as a Custom Data Validation on the cell itself (=myValidation(B12)), and I get an error: "a named range you specified cannot be found".

Can the function be used in this way or could someone suggest another way of handling this?

View 6 Replies
View Related
Dec 24, 2008

I currently have a spreadsheet that grabs data from 10 columns using vlookup.

This data is transferred to the new sheet in individual cells. (see attatched)

what i want to do is create a list from them cells, filtering out duplicates & counting them.

Creating a list like this:

720x560 = x5

452x282 = x3

etc.. etc...

I've tried everything i can think of, but i'm imagining that its a vba job to do what i'm asking.

I'd appreciated it of someone could point me in the right direction.

View 7 Replies
View Related
Jul 22, 2009

i need to count the number of used entries in a range.

so fex the range is A1:A10 but there are only 2 entries what i search is the number 2

is there a VBA function or so doing that.

ps i have this range.Rows.Count but this is counting me the length of the range even if there are no entries..

View 4 Replies
View Related
Feb 10, 2014

I have this formula that I want to simplify:

=COUNTIF(Table1[t-5],"=NF")+COUNTIF(Table1[t-5],"=NO")+COUNTIF(Table1[t-5],"=NA")

I want the formulat to capture all situations in which either NF, NO and NA are in the range Table1[t-5]

View 5 Replies
View Related
Apr 15, 2014

I have got a spreadsheet with a database of people and all their details, Surname (E), Name (F), Gender (L), Date of Birth (M), Age (O), etc...

So I have a demographics sheet that I calculate ages, gender etc and would like to do the following 2 tables.

1. Working out ages between 2 specified ages that I input into set cells. E.g. I want all people between the ages of 13 and 18 years old (So i put 13 into cell A13 and 18 into B13. My formula will then sit in C13 and will Also count those ages. I want to do this so to be able to change the ages if needed

2. Working out Surnames that sit between an alphabetic range. So as I did with ages I can have "A" into A51 and "D" in B51 and it will give me all the surnames that start with those letters in a range between A and D (Including the 2 options I have in those cells).

View 9 Replies
View Related
May 14, 2007

I've got a database with a date header across the columns C1 to W1 [C1 value = '02/04/07 and W1 value = '30/04/07]

Each row represents a different person

For each person there can be one of skills: Maths, English, French, Science, Geography on each day. Most people keep the same skill for the whole month but some change multiple times within the month.

What I'm after is a formula which will return the total number of people who have had each skill for at least one day within a time specificed time period. The startdate criteria is in A1 and enddate criteria is in B1

E.g How many different people had Maths on at least one day for the period 16th April to the 20th April.

At the moment I've been trying to work along the lines:

=sumproduct((C2:W200>=datevalue(A1))*(c2:w200

View 9 Replies
View Related
Aug 8, 2006

Counting a variable range. Does anyone know why this is not working...

Dim r

r = ActiveCell. Offset(0, 12).Value = Application.WorksheetFunction. CountIf(Selection, "Standard")

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = r

View 6 Replies
View Related
Apr 14, 2007

Is there a way I can count the cells in a range up until a certain value and use this # as a variable?

View 9 Replies
View Related
Mar 15, 2013

Assume this Data in a spreadsheet

I want to count blank "PI" based on "Product name" & "Product date" occurence together

So I created intermediate field "IS Blank", and I dunno what formula can give me the below results

My obective to get this result

CountBlank for PI = 3

--------------------------------------------------------------------------------

Product Name..... Product Date.... PI...... IsBlank "PI" [Desired Formula output]

xxx .......................ddd ...............Blank.................. 1

xxx .......................ddd ...............Blank.................. 0 (counted above for same xxx&ddd)

xxx .......................ddd222 ..........Blank.................. 1 (PD changed to ddd222)

yyy ......................ttt............... Blank....................1 (another product,yyy)

yyy .....................ttt ...............Blank......................0 (same product and date, so not counting again)

View 9 Replies
View Related
Jan 29, 2009

I want to auto fill certain columns in sheet A from sheet B & sheet C based on conditions

For Eg:

Sheet A:

Position#

Position Name

Personnel Name

Personnel No.

AB1

Manager

AB2

I want to auto fill in Sheet A for the columns:

Personnel Name

Personnel No.

Conditions:

If the employee is active in Sheet B then fill in Sheet A to the related position No.

Else

If the employee is mapped to org. (Yes) in Sheet C then fill in Sheet A to the related position No.

Else

Vacancy

i have written the below formula but not working properly:

=IF(VLOOKUP(SheetA!A4,Direct!A$2:K$16,5,FALSE)="Active",VLOOKUP(SheetA!A4,Direct!A$2:K$16,3,FALSE),IF(VLOOKUP(SheetA!A2,Contractors!A$2:K$15,5,FALSE)="Yes",VLOOKUP(SheetA!A2,Contractors!A$2:K$15,3,FALSE),"Vacancy"))

View 9 Replies
View Related
May 30, 2008

I have a rolling calendar worksheet that I need to create a formula to auto populate the sum of 8 weeks out, 4 and 8 weeks back based on any work week date I may plug in. So on the "summary page" based on a work week date, I need 8 weeks out, 4 and 8 weeks back projections to be auto summed for 10 different part numbers. The "13 week rolling" page will continue to be updated so that everyweek another week is added and the last week will fall off. (this part can be done manually if no function can delete the oldest column) When the weeks are added it must include the workweek in question. i.e. ww25 = (8weeks out) ww25:ww32 / (4weeks back) ww25:ww22 / (8weeks back) ww25:ww18. see example workbook attached.

This has been driving me nuts for a few weeks now.

View 2 Replies
View Related
Aug 6, 2014

In column A I have a list if places that can contain duplicates ie

Manchester

Birmingham

London

Birmingham

London

Manchester

Manchester

London

In column B through to D a list of statements to which there are multiple answers i.e.

Yes / Maybe / No

What I'd like to know is how many 'Yes' answers are in the data range for column B:D in Manchester

I've used a countifs but have to result to multiple countifs adding each column together which is fine for 3 columns but not when there are 50!

View 5 Replies
View Related
Feb 12, 2014

I'm looking for a way to count entries of numbers per day of the week within a date range. Example

Start End Mo Tu We Th Fr Sa Su TOTAL

2/10 2/23 1 1 1 1 0 1 1 12

3/1 3/31 1 2 1 1 1 1 2 40

I don't know if that possible in excel or its to much

View 6 Replies
View Related
Oct 16, 2008

I have a column of start times which are entered as per 24hr clock and what I am trying to do is to is to sort the times out into ranges;

00:01 to 06:59, 07:00 to 19:00 and 19:01 to 00:00

The range is T7:T488 and here is my first formula for 00:01 to 06:59 which works, it correctly finds 35 entries;

View 4 Replies
View Related
Jun 8, 2009

I have a column, we'll say E18:E2500. In the cells in that column are four digit numbers. Some of these cells may have multiple four digit numbers separated by a comma and a space. (example: 2020, 2100, 3120) Some other cells in the column may also share the four digit numbers (I mean duplicates).

So I'm trying to write a formula to sum and count all the unique values in the cells and in the range. This is what i've been trying to use but it counts all the values with no regard to duplicate values:

=SUM(IF(LEN(TRIM($E$18:$E$2500))=0,0,LEN(TRIM($E$18:$E$2500))-LEN(SUBSTITUTE($E$18:$E$2500," ",""))+1))

View 5 Replies
View Related