Count Cells Based On Conditions In That And Other Cell(s)?

Jan 28, 2013

I have an issue log containing (separate) columns with dates, numbers and text.

One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)

I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).

After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.

So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".

After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.

View 3 Replies


ADVERTISEMENT

Count Unique Occurrences Based On Multiple Conditions In Another Cells

Sep 19, 2013

I am trying to count the number of unique entries in a sheet, that also satisfy 2 other conditions.

I've attached an example sheet : Example email report.xlsx

The result I need is: The number of unique values in column E, that also have NULL in column G and NULL in column I.

So, in the example, the result would be 7.

I know I can do this by conditional formatting and filtering, but would prefer a formula, and perhaps also a macro that I could apply as the number of rows in each sheet is up to 20000, and each month's sheet will be a different size.

View 8 Replies View Related

Set Cell Fill Color Based On Conditions In Two Cells?

Nov 22, 2012

I want to highlight the cell of Column 'A' of excel only when it does not contain the text like "Verify", "Validate" or "Evaluate" in its content if, the value in corresponding cell of Column 'B' holds the value 'Y'. Secondly, the column 'A' can't contains the words like 'Verify', 'Validate' and 'Evaluate' if the corresponding cell in Column 'B' holds value 'N'. So just need to highlight those discrepancies if its there.

Column A
Column B
Expected Action

Press F3
N
Its Fine

Verify this..
Y
Its Fine

this....
Y
Need to Highlight Cell of Column A as Value in Column B is Y but the column A does not contain any value like "Verify", Validate" or "Evaluate"

Verify This.
N
Need to Highlight Cell of Column B as the Value of Column A contains words like "verify", "validate", "evaluate" but corresponding cell value in column B does not hold value "Y".

View 3 Replies View Related

Populate A Cell Based On Conditions Of Other Cells W/out Putting A Formula In The Cell

Oct 10, 2009

Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?

View 14 Replies View Related

Colour Cell Based On Few Conditions / Cell Values Of Other Cells In Same Row?

Jun 28, 2014

In the xls for each step I have 2 raws-planned and actual. Step planned duration is populated manually over the weeks.Before that row we have another reflecting the actual step status per week

I would like to find a way how cell reflecting the actual status of a step can be automatically populated (coulored) based on the colour/value of activities that are planned for that week and for that step.Activities are listed below the step and again have planned and actual row.

The rule should be : if for a week we have several activities all of them should be finished in order to have step stataus auto populated as green. If a single activity planned for that week is not done-then weekly step status should be red.

The activities for each step are grouped below the step. It seems that one of the difficult part in that request would be how formula will understand where starts and finishes the activities that belong to one step. To get that happen I placed a column showing step and another column where we activity.

View 9 Replies View Related

Count Occurrence Based On Two Conditions?

Sep 24, 2013

I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".

View 1 Replies View Related

Count Observations Based On Specific Conditions With VBA

Sep 11, 2012

I would like to write a macro that would count observations from a database extract based on specific conditions and generate a report.

My database extract looks like this :

Client name
Info1
Info2
Info3

Bob
aaa
aaa
aaa

[Code] ....

I would like to have an efficient and simple VBA code to count the observations. Something that would store the result of query like those:

# 'client name' = Bob : 2
# 'client name' = Bob AND info1 = aaa : 1
# Info3 = bbb : 2
# 'client name' = Bob and Info1 = aaa OR Info1 = bbb : 2

I am aware of DCOUNT function, but I am not too sure how to translate it in VBA. I also think that using autofilter and counting the visible rows may be subject to bugs.

View 9 Replies View Related

Count Data In Cells Using Two Different Conditions

Nov 3, 2009

I have a need for a formula that is able to count the data in column that coresponds to data in another column.

Example work sheet attahced for a quikie look.

View 8 Replies View Related

Count And Sum Cells Meeting Two Conditions

May 20, 2007

I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year.

I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color.

Next, I fill in each day with an employee name.

Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this:

Jones:
Friday 4 (total number of days jones is in a blue box)
Saturday 5 (...on a green box...and so on...)
Sunday 3
Holiday 2
Monday-Thursday 50

For each employee name. Sounds easy, right? I can't get it to work!

View 14 Replies View Related

Count Cells Using Multiple Conditions

Jul 1, 2008

I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.

I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes

View 4 Replies View Related

Count Number Of Unique Values Based On Multiple Conditions

Feb 22, 2011

I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.

In excel is best illustrated what I mean!

In total, 5267 lines and need to do this with all. You can do this in excel?

View 4 Replies View Related

Count Cells Based On Cell Color

Apr 14, 2014

I would like to count the number of orange cells, Green cells, Blue cells in the attached spreadsheet.

I have attached the sample file.. In my original file the cell colors will be a result of some conditional formatting.

Color Count test.xlsx‎
Test2.xlsx

View 3 Replies View Related

Count Number Of Cells That Meet Specific Conditions - Error Messages

May 11, 2006

I have a spreadsheet which is linked to several other worksheets. I have managed to include formulas to count how many cells have numbers between 101 and 5000 by using this formula -

=sum((h2:h500>=101)*(h2:h500<=5000))

but now I want to count the number of cells in another worksheet that are equal to or less than zero. When I use the same formula as above it counts all the blank cells. I have tried using a countblank formula and then deducting this from the result, but unless the other worksheet is open the countblank formula does not work.

View 9 Replies View Related

Count Cell Value If Conditions Met

Jan 22, 2009

I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.

View 4 Replies View Related

Count A Cell Only If It Met One Of Three Conditions

Apr 13, 2007

What formula would I use to count a cell only if it met one of three conditions?

ie only if the cell equalled 1, 2 or 3 but not 4?

View 9 Replies View Related

Color Cells Based On Multiple Conditions Of Different Cells

Jan 15, 2008

Im trying to change color in cell B9 according to different conditions in Cell C9 (than on b10 according to c10 and so long until necessary - probably will be around 2000 lines). Since there are more than 3 conditions and in two cases condition depends on the color of the C Column, I can not use conditional formatting. Ive searched and found similar forums here, but since Im ignorant in VBA code, I couldnt manage to make adjustments. So if you can help me with the code, I would really appreciate it!

Condition and Results required would be:

IF column C = S than on Column B = color cell light blue with white border
IF column C = P than on Column B = color cell Green with white border
IF column C = A than on Column B = color cell Yellow with white border
IF column C = L than on Column B = color cell Red with white border
IF column C = C than on Column B = color cell Dark Blue with white border
IF column C = than on Column B = color cell white
IF column C = V and has white background than on Column B = color cell white
IF column C = V and has a Dark Grey background than on Column B = color cell Dark Grey

I dont know if the last two are feasible.

View 6 Replies View Related

Highlighting Cells Based On Several Conditions Using VB?

Feb 27, 2013

I am writing a data validation macro which checks various conditions and highlights cells if they are incorrect.

i.e. If cell in column D = "specific text", then cell in column AH of the same row must = "specific number". If it is not that number, colour the cell red. If another cell in column D = "another specifci text", then cell in column AH of the same row must = "specific number". If it is not that number, colour the cell red.

What I've found is that I can get one condition of this nature to work, but if I run two or more in the one macro, that they supercede eachother (and thus the final one is the only one that works).

See below sample code.

Code:
Dim val As Integer
val = UsedRange.Rows.Count
For i = 2 To val Step 1

[Code]...

View 4 Replies View Related

Make One Cell Count Twice In AVG If Certain Text Conditions Apply- 2008

Mar 9, 2009

I am using Excel to tabulate votes for a contest. Judges have given a number to each entry, and but certain judges' opinions need to count twice as much as other judges' opinions based on their qualifications.

I've attached the file to help illustrate what I'm trying to do. Morris's votes need to count twice for all Photography or Web Design entries, and Clark's votes need to count twice for all Graphic Design or Web Design entries.

I know I can do this manually by simply copying the number into a blank cell in another column (like the blank column between Morris and Clark's names), but is there any way to make Excel do this for me?

View 7 Replies View Related

Adding Non-sequential Cells Based On Conditions

Nov 2, 2009

I'm trying to write a formula where I can add the values found in non-sequential cells in a row based on if the value in the previous cell in the same row meets certain criteria.

View 14 Replies View Related

Concatenate Cells Or Ranges Based On Conditions

Oct 1, 2009

I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.

"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)

In another cell, i would like to merge them together like so

"A01-A07" are for Joe Bloggs

And also as follows

"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith

would like this to be
"A01, A03-A05,A07" are for John Smith.

So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.

View 4 Replies View Related

Copy Cells To 2nd Sheet Based On Conditions

Apr 17, 2012

I have 2 worksheets (X and Y) and am trying to write a macro/vba to copy certain cell values from a row if that row has a marked cell. For example, on sheet X, marked cell is column P and when P = "Y", then values from cells B, D, I, J, L need to be copied to sheet Y.

Sheet Y has multiple rows and the values from X need to be copied into appropriate rows. B from X goes into A from Y, D into B, I and J into C, etc.

Im not sure how to go about this other than have every cell copied and then filtering sheet Y. But Id like to have a macro only copy marked rows and appropriate cell values (some contain formulas) as the data is constantly changing and sheetY has to be printed up.

View 3 Replies View Related

Delete Cells Based On Multiple Conditions?

Jul 16, 2013

I'm trying to remove cells that contain certain suffixs. Here is the code I'm trying to use:

Code:
For i = 7 To 2000
If Cells(i, 7) = "IV," Or Cells(i, 7) = "Jr," Or Cells(i, 7) = "III,"
Then Cells(i, 7).DELETE Shift:=xlToLeftEnd IfNext i

but it does absolutely nothing. I've used this for similar macros in the past and it worked just fine.

View 4 Replies View Related

Color Cells Based On Entry. More Than 3 Conditions

Nov 21, 2006

I was hoping there was an alternative to having to use so many named ranges for each sublist (there would many sublists in the actual workbook). Anyway, I'm a little uncertain about the code you posted - do the LIST and SUBLIST codes go in Source in Data Validation, or in Source in Dependent Validation Lists Based On Header Values? Can you give me a bit more instruction on this? Also, I'm trying to use the following code to color cells upon a change in value in V1 or V2, but can't get it to work. Can you kindly tell me what's wrong with it?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "V1" Or "V2" Then Exit Sub
Select Case [V1].Value
Case "A"
Target.Interior.ColorIndex = 40
Case "B"
Target.Interior.ColorIndex = 35
Case "C"
Target.Interior.ColorIndex = 36
Case "D"
Target.Interior.ColorIndex = 34
Case "E"
Target.Interior.ColorIndex = 19
Case "F"
Target.Interior.ColorIndex = 24
End Select
End Sub

View 7 Replies View Related

Color Cells Within Matrix Based On Conditions Outside

Jun 5, 2008

In 'MAIN REPORT' there are thousands of columns of data, of which the important columns are 'Factory No.' and 'Row No' and 'Quantity'.

Each factory has its own worksheet as well.

In each of those factory sheets there is a 15x65 grid for each of the 65 rows that are mentioned in MAIN REPORT.

I have created and IF statement that checks if there is any data in 'Quantity row' and if there is it enters a "1" in the that cell of the virtual Factory grid.

This is the formula:
=IF('MAIN REPORT'!I2="","",1)
However, this simply checks that data while there is only 1 quantity for each row of each house.

Sometimes there will be different quantities (for diff. products), and I need a formula/macro that checks the line no. column and then,

If there are 5 items in row 7 for example then it will place five "1"'s in the first five cells of row seven in the corresponding factory sheet.

View 3 Replies View Related

Populate Cells With Data Based On Conditions From Ranges

Oct 13, 2009

What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office

View 2 Replies View Related

Count Data In Column X Based On Conditions In Column Y

May 6, 2009

I am wanting to count the number of records (excluding cells with no value) based on criteria in a corrosponding column. In column "AS" I have a number of records that are not sorted showing values "7", "13" and "2".

In column "AL" there are values attached to some of these records based on certain IF statement conditions. I am wanting to count and sum the number of records in column "AL" that meet the conditions of "7" in column "AS" and so forth.

View 3 Replies View Related

Conditional Sum & Count: Count Request Matching The Crateria Of Date And Other Conditions

Nov 17, 2007

see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.

View 2 Replies View Related

Multiple Conditional Format Conditions Changed Based On Another Cells Value?

Jun 12, 2009

A1:A6 have numeric values 1 to 6.

I want the conditional format values to change Based on the value in Cell B2.

If the value in B2 is "old", then I want these conditional format conditions for A1:A6. Numbers between 1-2=green text, 3-4=orange text and 5-6=red text.

IF the value in B2 is "new" then I want the conditional format conditions in A1:A6 to change so 1=black text, 2-3=green text, 4-5=orange text and 6=red text.

I know how to do multiple conditional formats but i have no clue how to change multiple conditions based on another cells value.

View 6 Replies View Related

Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsm‎

View 10 Replies View Related

VBA To Set Cell Value Based On Multiple Conditions

Jan 4, 2013

I am creating a document log that tracks all excel files sent and received.

I use RDBmerge to get the filenames and data from the file batches.

My current macro edits the data down to the last stage of data needed to create the log.

My example workbook shows the final stage of the RDBMerge Data "Rough_Data" and then the final data formatted needed for the Log "Final_Data"

Included on the "Rough_Data" worksheet is the Code log used to complete the "Final_Data" worksheet.

I have highlight the cell range on the "Final_Data" Sheet that I need to complete.

filenames can occur multiple times from the RDBmerge, so only one occurance of the filename is place in column B of the "Final_Data" worksheet.

The criteria is this:

A column on the "Final_Data" worksheet as been Named for Each of the possible "Record Types" from Column G of the "Rough_Data" Worksheet. ("A,B,C,D,E,I,O,P,Q,T,V,W,X,Y")

I then must look through the "Rough_Data" worksheet for the first occurrence (if any) of that record type for each filename on the "Final_Data worksheet. If an occurrence is found I then must look at the "Unique ID" & "Program" Columns of the "Rough_Data" Worksheet. The key (on the "Rough_Data" worksheet) is used to determine the code that is written to the corresponding cell.

i.e.
"Final_Data"Date
FileName
Field2
User
Direction
Method

[Code] .....

I would very much like to automate this process, as sometimes I am dealing with over a hundred files with 30 plus rows of data each.

View 9 Replies View Related







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