# 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
ADVERTISEMENT
Nov 17, 2009

I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.

When "dates" or J2:J25 is less than or equal to today's date

AND

"HTeam" or W2:W25 is equal to Civil

AND

"Percent" or K2:K25 is equal to 100

View 4 Replies
View Related
Apr 22, 2014

I have used Formula to ID cells containing a formula to flag cells red with conditional formatting in a list that do not have formula.

I need a single cell to change colour if any of the cells in the list do not have a formula. e.g. the subtotal in the top row could go red so the user can scroll down to find individual red cells.

Is there a way to count the red cells without formula in the list - Conditionally formatted cells are hard to count. I don't want to use a vba script to do it as the user probably won't run it and auto run slow calculations down.

Is there an array formula that can return true or false if not(CellHasFormula) is true anywhere in the list?

countif(AH1:AH2976,CellHasFormula) doesn't work.

View 9 Replies
View Related
Apr 30, 2009

Hello, we have a stop light spreadsheet for status with several different projects. In each row there are conditional format status green - good, yellow - needs work, red - bad...based on data from another tab.

There is an "overall status" column that pulls the worst color that exists for a given row.

=IF(COUNTIF(F1:V1,"r")>0,"R",IF(COUNTIF(F1:V1,"y")>0,"Y",IF(COUNTIF(F1:V1,"g")>0,"G","")))

There is also a date for each row. Now we want to pull the worst color in the "overall status" column for a date range...and display that on a separate summary tab.

For 4/1/09 thru 4/15/09 - pull the worst color from the "overall status" column from that date range.

View 10 Replies
View Related
Feb 12, 2009

The data:

There are a number of columns, but we'll use the first 2 first. Column A has the dates from 1/1/2009 to 12/31/2009. Column B will has numbers, mainly 0-100. One cell will have a 'report date' which is just a date.

The formula needed:

I have a few cells labeled wk1, wk2, wk3. Week 1 will start with the 'report' date. wk1 I would like the Average of Column B IF the dates in Column A are within the 'wk'. wk1 = report date TO report date + 6. wk2 = report date + 7 TO report date + 13 and so on.

My goal is to have the formulas all set so that when we move to another report date and the raw data is added, I can just change the 'report date' and everything (the wk numbers) will automatically update with the new raw data.

The furthest I got was this, but it didn't work.

=AVERAGE(IF('A3:A33>=VALUE(D3)

View 9 Replies
View Related
Nov 1, 2009

i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.

Public Sub offloadDoor()

Dim unassigned As Long

unassigned = 0

For rowvar = 18 To 504

If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then

If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then

unassigned = unassigned + 1

End If

End If

Next

Sheet8.Cells("b3").Value = unassigned

End Sub

View 5 Replies
View Related
Aug 4, 2005

I have a table in Excel:

The first row is time in years.

The second row is method name,say,"A","B","C".

I want to count the number when the time is less than 5 years AND "A"

method is adopted.

I tried this:

count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.

how to revise the formula?

In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as

countif(C2:Z2,"<5")

View 9 Replies
View Related
Jan 7, 2009

I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.

I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).

=SUMPRODUCT((C3:C124=1)*(D3:D124=0))

View 2 Replies
View Related
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
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
Feb 13, 2009

40,000 rows,

Column A is a Port Code . . . always 4 digits

Column B is a 2 digit code representing a mode of of transportation.

I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .

View 9 Replies
View Related
Mar 29, 2007

There are a variety of tabs on this database. These tabs track the large customers and specific brands.

In the “Sales Rep Calendar” tab, I have attempted to calculate how many quotes are established 1) per Month and 2) per OS Sales Rep for 2007.

Using formulas such as: ...

View 6 Replies
View Related
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
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
Feb 16, 2006

Ive started using the sumproduct function to count multiple conditions which is useful

howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?

the only way i can think is like the below

=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))

Rather than having to eliminate red and yellow i would like to say is green or blue.

View 14 Replies
View Related
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
Feb 27, 2014

I am struggling to come up with a formula that will look at one column of data to check for a condition (for example, it says 7. Implement) and then look at another column to see if a second condition is met (this one is a date ie Feb-14) and count those that meet both. The data I wish to check is on a different worksheet if that makes a difference...

View 1 Replies
View Related
Apr 25, 2008

I am trying to count the number of entries in range BH3:BH621 when the cells in range B3:B621 = "Acting" and the range D3:D621 = "Feb"

I can do it with either the B range or the D range, but not both together.

View 9 Replies
View Related
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
Aug 29, 2013

Currently I have 3 Columns. The first column is a list of phone numbers that have confirmed and bought our product. The second column holds the phone numbers of the initial leads that we generated. And the third column is the keyword that the lead used to find our website.

What I need to do is match the phone number from Column A with the phone number of Column B then record the frequency of the keyword that was used to produce that lead.

I've attached a screen shot with the first few rows of my table (Nearly 1000 in total) as well as an example of what I imagine the result looking like.

First Few Rows Of The Table:

Excel-Keyword-Matching.jpg

What I Am Looking To Do:

Keyword-Frequency-Complete-Example.jpg

View 2 Replies
View Related
Jan 6, 2007

For a banking spreadsheet to monitor monthly transfers:

COLUMN A - Date of transaction (entered manually)

COLUMN B - Description (transfers entered manually as "T")

If value of cell in COLUMN A is within current month, then Count corresponding cell in COLUMN B when "T"

View 9 Replies
View Related
Jun 25, 2007

I have been asked to analyse the contribution / performance of each student in a team. What i am trying to do is to count the number of times each student has work together and then try to work out their contribution. Got stuck on the 2nd bit for like 3 weeks flat. have been trying but not luck... seems to give me 1 or 0. so i assume my attempt is only a true or false answer! All i need is a count of how many times each student work with each other, and the best backbone formula is "count". This situation is best explained by my example, D15 is the closest i got

View 3 Replies
View Related
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
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
View Related
Apr 12, 2014

I'm trying to count the number of occurrences where two conditions in a table are true.

I have a table that has two columns for ratings; impact and probability. Each can be scored 1-5 This creates a matrix table of possible scores from 1 - 25 (image attached)

I want to COUNT the number of items in each of the boxes (not the total score). For example, how many are Impact 5 and Probability 5 (25 total); how many are Impact 4 and Probability 2 (8 total), and so on. Basically a count of the each of the intersections in the matrix.

Something like "Countif Impact is 5 AND Probability is 5"

Is it possible to count something once, checking for multiple conditions?

View 2 Replies
View Related
Oct 13, 2009

I have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.

For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).

The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).

For example: .....

View 10 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
Jun 4, 2008

I have a Sumproduct formula to count instances of a particular event (from a list of events) based on multiple criteria.

I am trying to utilize the same method to count instances of all events not defined in the list of events but I would welcome any solution

In the attachment,

Defined list of events A4;A5 (this is just an example, the actual list is approx 100 events)

Data being counted F2:N10 (actual data approx 1000 rows)

My working formula is in cells B4 through D5

My not working attempt to adapt the formula B6

View 3 Replies
View Related
Jun 2, 2007

I am using the below code to count records in a range that meet multiple criteria.

myCount=0

For Each dCell In Range("MyRange")

If dCell = var1 And dCell.Offset(0,-1) = var2 Then myCount = myCount + 1

Next dCell

Is there a faster way to count multiple criteria in a VBA procedure than what I'm doing, but without using worksheet formulas? I tried "WorksheetFunction.Sumproduct" like you would in a worksheet formula, but that didn't work (unsurprisingly).

View 4 Replies
View Related
Mar 20, 2008

Im trying count cells based on the cells color. My sheet has multiple blocks filled with different colors. I have searched all of the regular sites out there for vb code related functions, but I can find one that will let me select a range of colors and a range of cells to be counted.

View 2 Replies
View Related