Count If Value Is Over Certain Amount And Another Column Contains Certain Text?

May 6, 2014

I have a worksheet where

Column D lists a service offered by a business, such as IMC, TC, Respite, etc

Column M lists how long the customer has used this service for in weeks, such as 4,5,3.5,6 etc

What I would like to do in another column is to count the number of clients who have used the service for 6 weeks or more broken down by the service type i.e. Count how many people have used IMC for 6 weeks or more.

If possible I would also like to be able to count those who have used a service for between 5.1. and 5.9 weeks too, again by service i.e. Count how many people have used IMC for between 5.1. and 5.9 weeks.

View 2 Replies


ADVERTISEMENT

Count The Amount Of Cells In A Column

Jun 10, 2009

I am trying to count the amount of cells in a column (Col H) containing data against a certain date which is displayed in another column (Col B)

Eg.
Col B................-...........Col H
13/06/09.................FIRST LAST
13/06/09.................
13/06/09.................FIRST LAST
20/06/09.................FIRST LAST
20/06/09.................

So that in the sheet next to it it would appear:

13/06/09 - 2
20/06/09 - 1

The dates don't appear in row order and the sheet has a filter on it so I am unable to select the Col H and just use COUNTA

View 11 Replies View Related

Count Amount Of Cells Within Range That Contain Multiple Strings Of Text

Apr 22, 2013

I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below

gameballnetstick
ballgame
stick ball
tenballs
green stick

Of the three examples: it would only count cells: gameballnetstick and stickball

View 2 Replies View Related

Count Amount Of Times / Show Up In Column And Sum It At The Bottom

Apr 29, 2012

I am trying to count the amount of times 4, 5, and "11-5" show up in a column and sum it at the bottom. I tried

=sum(if(c4:c41="4"+c4:c41="5"+c4:c41="11-5",1,0)

As an array and keep getting N/A. So im not sure where my problem is.

View 6 Replies View Related

Count The Amount Of Entries Based On The Date In A Column

Jan 17, 2009

I have a spreadsheet containing 10,000 + entries.

Each Entry is Dated within Column D2:D10786 in this format - 1-Nov-08 (example).

Lets say i have a cell on another sheet Cell A1 and in this Cell i want it to Count how many Cells contain the dates from Nov-08 in my Date column..

View 9 Replies View Related

Find Column "Amount". Insert Column Next To Amount

Feb 22, 2008

I need some code to do the following.

Look at worksheet 1. Find column "Amount". Insert column next to amount.
For all highlighted cells in column "amount" insert text "R" in adjacent (inserted) column.

View 9 Replies View Related

Excel 2010 :: Count Distinct Text Values In One Column If Another Column Equals A Value

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

Count Column Based On Certain Text In One Column And Blank In Another

Feb 4, 2014

I need to count column E when it does not say "MTO" or if column G is blank. E.g. below would return me a value of 2. I would count row 1 and 3 only.

Column E Column G
10-Jan-14
MTO
06-Dec-13
18-Oct-1321-Oct-13

View 9 Replies View Related

How To Count Amount Of Days

Sep 1, 2008

Is there a formula to count the amount of days, for example.....a1=8/1/08 b2=8/10/08 then c1=9 days....

View 4 Replies View Related

Count Formula One Record With Amount Is E, F, &/or G

Jan 27, 2006

Need count formula to count records with amounts in either columns E, F, or G. For example

Need a formula (not VBA) in cell F2 to return a count of 5 records counted that have an amount in column E, F, or G (but only count as one record when amounts exists in multiple columns):

__|____E___|____F___|___G___
_7 | 1200.62 | 1500.53 | -0-
_8 | 1000.00 |________|_1620.00
_9 | 7000.00 |________|________
10 |________|________|________
10 | 2000.00 | 3000.00|________
11 | 8000.00 |________|________

View 8 Replies View Related

Count Duplicates As Unique Record, Sum Amount

Mar 14, 2007

I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total $ amount of each amount attached to each occurrence.

Please look at the sample to see what I mean.

W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record.

I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs.

Dim rCell As Range, rRng As Range, vKey, lrow As Long

Set rRng = Range("F2:F199")

With CreateObject("Scripting.dictionary")

.comparemode = vbTextCompare

View 8 Replies View Related

Count/sum Text In Column

Nov 9, 2009

Here I come with yet another question in excel, how do I count/sum text in a column. E.g
Column B
Fish
Eggs
Meat
Fish
Fish
Chicken
this would basically be # Fish 3, # Eggs 1, # Meat 1, and # Chicken 1. Hope I make it clear,

View 9 Replies View Related

How To Count Duplicate Text In A Column

Feb 4, 2012

how to count how many times a word or text is displayed in a column.

Say this is Column A Can any formula be used to tell me that T1409 was duplicated 3 times and T1527 was used 2 times. The rest of the numbers only triggered once so I dont care for those numbers. I only want T1409 and T1527 to pop up and tell me how many times it was used.

StoreT1409T1409T1527T1527T1360T2463T1547T0164T1976T2482T1409T2760T1507T0224T2458T0201T2398

View 2 Replies View Related

Count Column Of Text Excluding Duplicates?

Dec 5, 2012

I need to count a column of text excluding duplicates, but only if they are duplicates of the cell directly above them in the column. Example:

a
b
c
c
d
d
a
b

I want this to return 6 instead of 4. Is this possible?

View 9 Replies View Related

Count How Many Times A Particular Text Appears In Column?

Nov 25, 2013

I want to count how many times a particular text appears in Column A depending on the number times another text appears in Column B.

Say for example if I have in Column A {A, B, C, D}nd column B I have {AA,BB,CC) and if I want to check how many times column A has "A" value when the column B has "CC" value, then how should I proceed with this ?

View 3 Replies View Related

Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet

Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies View Related

Add Number 1 In Adjoining Column When Existing Column Has Amount Highlighted In Green Or Red

Dec 27, 2012

I have a spreadsheet with sales which I colour green when they are outstanding and red when they are despatched. I would like to automatically add a 1 in an adjoining column when the cell is green (eg outstanding) or zero when they are despatched so that it totals up the number of outstanding orders.

a b

172.95 (this column would be green)
024.00 (this cell would be red)
124.00 (this cell would be green)
132.25 (this cell would be green)

View 3 Replies View Related

Count Partial Text Matches In Adjacent Column?

Aug 11, 2014

I want to count the number of times partial text strings occur in a cell adjacent to another cell containing specific text.

A B
FB Milton v Town PHOTO
BS Fairfax v South
BS North v Town
BS Milton v South PHOTO
FB North v Milton PHOTO
FB Milton v South

I'm looking for the number of times "FB" and "Milton" occur in Column A when "PHOTO" also appears in Column B in the same row. From numerous searches I've tried figuring out VLOOKUP, DCOUNTA, etc.

View 6 Replies View Related

Count Number Of Times Text Combinations Appear In Same Column

Oct 6, 2013

A1:A10 contains text (say colors) and B1:B10 also text (say vegetables). I need a formula to count the number of times a certain combination of numbers and vegetables appear in the same column, so if "red" and "carrot" appeared in A4 and B4 and also in A6 and B6, the result would be 2.

View 4 Replies View Related

For Each Loop To Count Text In A Cell With Multiple Criteria In Single Column

Dec 19, 2011

Is there any method to speed up a for each loop to count text in a cell with multiple criterias in a single column. This is on example:

Code:
For each rr in r

If rr = "a" And rr.Font.Strikethrough = False Or rr = "B" _
And rr.Font.Strikethrough = False Then
a = a + 1

end if
next

View 1 Replies View Related

Count / Sum Numbers In Column Where Specific Text Exists In Adjacent Cell

Jan 19, 2008

One has numbers the other has "YES" or is blank. I want a formula to look at the second column and where there is a "Yes" then count the number in the same row in the first column.

View 4 Replies View Related

Count Where Text Value In 1 Column & Month Of Dates Meet Condition/Criteria

May 23, 2008

need a formula to calculate the total number of x's in one column (column B, C, E are training types= x) where the corresponding column date falls within a date range. It’s to total each type of training done for each month. I have 3 training type columns and a cell that calculates the total number of trainings for the each month:=COUNTIF(F2:F100,">="&DATE(2008,2,1))-COUNTIF(F3:F200,">="&DATE(2008,2,31)).
So now I just need it broken down by training type per month. How many x's in each column for February as an example.

View 7 Replies View Related

Count If Column A Contains Defined Text And Column B Equals Defined Word

Mar 25, 2014

formula that would count the instances in Column C, where Column A contains "LH" or "RH" as part of the word, and column B equals "Either".

View 5 Replies View Related

Getting The Amount From Between Text And A Date

Aug 25, 2009

a1: "house $350.000 11/2/08" or "house 350.000 11/2/08"

should retun in b2 -> 350.000

anyone here?

View 14 Replies View Related

Date When Certain Amount Of Text Occurs?

Nov 9, 2013

I am trying to create a formula which would display a date (prior to the computer's system time) when a certain amount of text occurs.

I have attached my workbook with an example and further explanation.

View 14 Replies View Related

Converting USD Amount From Number To Text

Oct 31, 2013

I have text amount in L55 column. The amount is auto summed amount. Example, $ 5081.16

Now in C59 column (total FOB) I have to write the amount in text manually. Any formula to make the amount converted in text automatically??? I badly need this. Attaching image.

THE Format of text amount has to be like below.

ex: FIVE THOUSAND EIGHTY ONE AND SIXTEEN CENTS ONLY.

It has to be caps locked, there will be and between cents and main amount, there wil be cents after that. Attaching image.

5081.JPG‎

View 6 Replies View Related

Formula- To Calculate The Amount Due Based On Cumulative Sales Once A Breakpoint Amount Is Reached

Jan 28, 2009

I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.

Example:

Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%

month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00

and so on...until the end of year.

I tried using an if formula by could not get it to work.

View 9 Replies View Related

Add Amount In Column

Apr 1, 2009

Trying to add a formula in my spreadsheet that will add every amount in column AQ8:AQ200 if the date in AQ8:AQ200 is march.

The format for the date is 3-12-09.

Here is what I'm trying.

=SUMIF(AU8:AU200,MONTH=3,AQ8:AQ200)

View 6 Replies View Related

Reference Cell & Add Amount If Positive & Subtract Amount If Negative

Jun 5, 2008

Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:

I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.

View 4 Replies View Related

Adding Row And Column Amount

Jan 29, 2014

I need a formulas to add the row & column amount , see the attached example sheet.

Row and Colums.xlsx‎

View 1 Replies View Related







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