# Formula To Count Two Criteria In One Column If There Is Third Criteria In Another Column

Aug 21, 2013
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.

View 4 Replies
ADVERTISEMENT
Dec 8, 2008

I am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...

=COUNTIF(G1:G100,"AND(>10,<20)")

View 3 Replies
View Related
Mar 10, 2014

I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.

View 1 Replies
View Related
Aug 28, 2007

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

View 4 Replies
View Related
Oct 31, 2013

I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:

untitled.jpg

I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.

Non-VBA preferred

View 4 Replies
View Related
Dec 6, 2013

Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.

test book 1.xlsxâ€Ž

View 3 Replies
View Related
Jul 25, 2012

A column W5:W9999 contains attendance from either of 0, 1, ........31 of employees.

How to count no of employees meeting certain criteria like:

Total no. of emp with values >0.

Total no. of emp with values >0 but < 16.

View 9 Replies
View Related
Mar 10, 2014

count cells row wise that meets criteria both in column & Row as well.

View 2 Replies
View Related
Feb 14, 2008

As the title says, I need to count the number of unique records (names) in column A, where column L is = to something specific (X,Y,Z,W, whatever) for some statistics im trying to report.

Please see the attached spreadsheet. For role X statistics, I need a count of the unique names from col A, where col L = X. Based on my sample spreadsheet, the number should be 2. For Role Y, it should be 3.

There is the potential for spaces in the rows, and no one will be 'cross role'

I've got a few different methods to just count unique values with specific criteria in the same column, but I just can't make anything work for specific criteria in another column.

View 9 Replies
View Related
Jul 28, 2006

I have data (coming straight out of another system, so whilst is seems messy, its the easiest way to have it in Excel).... where there are a number of rows (one for each employee) - then every column represents a date in the month (so there are about 30 columns)....the cells contain a number of hours worked by that employee for that day.

I have figured out how many hours they worked for each day of the week, but my client now wants to know:

for each employee - how many Mondays did they work?- how many Tuesdays? and so on

I am happy to figure out the day of the week for the first date in the month and then manual set up another column heading above the date that tells me what day it is - but I feel that Excel may actually be able to work out what day of the month it is - although the dates come across from the other system as text - April 26, 2006 etc...

So I would like a summary in my sheet that has column headings

Mon, Tues, Wed, Thurs, Fri, Sat, Sun

and then for each row for each employee - I would like the number of days that employee worked for the month by these Days of the Week.

I have looked at Count if - but I need to be able to select the range of cells to count, and also the range of cells to evaluate for the criteria and I cant figure it out!

View 9 Replies
View Related
Feb 5, 2013

I get this work a lot and am looking for a much more effective way of working the dataset,

I have numbers like 1.1, 1.2, 1.3 and so on.......

These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....

I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.

I am attaching a sample document to see how it is laid out. [URL] .....

View 6 Replies
View Related
Aug 13, 2007

I am trying to count the number of times a code appears in column N, IF the corresponding cell on column T is blank. Column T either has a date or is blank and column N has a 4 letter code.

This is what i have been trying.

=SUMPRODUCT((CMRF!T:T=ISBLANK)*(CMRF!N:N="B2"))

I want the code in column N to match to the code currently in cell B2, i have also tried using "" instead of ISBLANK but i get # NUM! error as a result either way. the result should just be a number i.e. 400 (cells with the same code as B2 and no date in cell T)

View 9 Replies
View Related
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
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
Nov 21, 2007

Im trying to construct a nested Countif statement. I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N. The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"

View 2 Replies
View Related
Mar 24, 2014

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

View 1 Replies
View Related
Apr 22, 2014

I am looking for a formula as per the thread title. I have attached an example workbook.

I have been playing around with SUMIFS and SUMPRODUCT but can't crack it.

sum based on criteria transpose columns to rows.xlsx

View 5 Replies
View Related
Aug 25, 2014

I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).

Sheet 1 (order)

A

052611

052806

052843

Sheet 2 (005)

__A_____B____C_____D_____E____ F

052611_________________________N

154272_________________________Y

125485_________________________Y

052806_________________________N

125478_________________________N

052843_________________________N

how I can write is in a formula?

View 7 Replies
View Related
Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function

Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

View 5 Replies
View Related
Nov 29, 2013

I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.

What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.

Sample Workbook.xlsxâ€Ž

View 12 Replies
View Related
Jun 2, 2009

I have a column of cells (Column I2:I1063) with zip codes in it and I want to keep the row if the zip code matches one on the list in a column B2:B100 on a separate sheet (Sheet1).

View 4 Replies
View Related
Feb 26, 2012

I'm trying to find a formula that will find an original entry using 4 criteria original entry is cases ordered.

columns A,B,C,D will have to match then give me a result in column E to find original entry if there is one otherwise I will have to enter new row and original entry for cases ordered.

Eg.

A = section

B = description

C = size

D = region

E = cases ordered

Row 2 = fruit, apples, medium, north, 25

Row 45 = fruit, apples, medium, north,

View 3 Replies
View Related
Apr 24, 2014

i have the following code, what it does is, it locate those empty cells in column M and insert the formula "=TODAY()". What i need the code to do is only insert to the empty cells in column M if there is a value(as long as is not empty) in the reference cell of column E.

VB:

VB:

Private Sub CommandButton3_Click()

Dim wks As Worksheet

Dim rng As Range

[Code].....

View 7 Replies
View Related
Jan 9, 2013

I have a rolling 12 month (each day in column) tab in Excel 2010 that references variances by ID number in column A, and the column headers are each day for 366 days (2011). I would like to create a tab that shows the past 10 day's variances by ID number. I haven't been able to write a formula that will look at the date headers and the row ID number to return the figure for that day. Here is a sample of the 12 month rolling and the 10 days at a glance that I want to populate.

Rolling 12 Months

IDName12/25/201212/26/201212/27/201212/28/201212/29/2012and so on

1234Employee Name - - - - (11.07)

1235Employee Name - - - 0.20 -

1236Employee Name - - - - -

1237Employee Name - - - - (1.00)

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

View 4 Replies
View Related
Jun 7, 2014

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A

B

C

1

Cat 1

January 1, 2014

John

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

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example;

C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1

C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1

C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

The best try I had for the formula in C7 was

{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

View 2 Replies
View Related
Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

CriteriaHeader 1Header 2Header 3Header 4

ZZZ5.0015.0050.00130.00

ZZ5.0015.0050.00130.00

Z5.0015.0050.00130.00

YYY5.0015.0050.00130.00

YY5.0015.0050.00130.00

Y5.0015.0050.00130.00

DDD5.0015.0050.00130.00

DD5.0015.0025.0075.00

D5.0015.0020.0065.00

RRR5.0015.0015.0045.00

RR2.5010.0010.0030.00

R1.503.0010.0025.00

UUU0.751.505.0020.00

UU0.751.505.0020.00

U0.751.505.0020.00

P0.751.505.0020.00

T0.100.105.0018.00

CriteriaNumberValue

DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))

View 3 Replies
View Related
Dec 8, 2005

Col C = Text and Col F = dates

I would like to count the # of times a value occurs in Col C based on a date

range in Col F.

View 11 Replies
View Related
Dec 20, 2012

I am looking for a way of creating the following conditioned concatenation.

I have two tables, let's call them "summary" and "detailed".

The "detailed" table is something like the following:

ID

VOL

001

01

001

05

[code]....

The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:

ID (unique)

VOL (concatenated)

001

V01, V03, V05

002

V01, V04

003

V06

PS: I have people using this table with office 2003, so compatibility is necessary...

View 1 Replies
View Related
Jan 30, 2013

If i have the following layout of data

Column 1 Column 2

January 1000

January 1234

February 1300

March 1600

January 15

March 123

April 234

January 3000

I would like a formula that adds all the January numbers together returning a result of 5249

To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.

I'm sure i have done this before using a countifs maybe but my mind has drawn a blank

View 1 Replies
View Related
Jan 13, 2008

I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...

View 12 Replies
View Related