Countif With Conditions From Two Columns?

Dec 5, 2013

I have coded data for age range in one column and for answering yes or no to a question in a different column. I need a formula that will count the number of yes answers for each coded age range.

a= 14-15 years old, b=16-17, ect.

Column One Column Two
age(coded) Yes or No
a Y
a N
b N
c Y
a Y
b N

I need to be able to count the number of yeses for each coded age. If the formula works it should give me the following output given the sample data above.

14-15 year olds output with the data above would be 2

16-17 year olds output would be 0.

How would I write this formula? I tried countif and sumproduct (though admittedly sumproduct I don't know at all and just tried to find a similar situation and tried it with my spreadsheet).

View 5 Replies


ADVERTISEMENT

Countif Multiple Conditions: Look At Two Columns And Set A Criteria To Count

Jan 15, 2010

I want to be able to look at two columns and set a criteria to count. I want to look at column A and if its blank then look at column B and if it has a value of more than 0 then count.

A B
1 1.00
2 Yes 4.78
3
4 5.00
5 Yes 4.89
6 11.99

So this example would count 3

View 3 Replies View Related

Countif (check The Data For The Following Conditions, If It Meets The Crirteria Then Place A 1 In Columns)

Aug 22, 2009

the traditional count if statement doesnt return what I need. I have an array of values that need to be checked.

Column: A B C D E
Data: .25 .49 .18 (Criteria 1 Result) (Criteria 2 Result)

What I need to do is check the data for the following conditions and if it meets the crirteria I need excel to place a 1 in column D or E.

Criteria 1
If any of the coulmn data contains a value less than .5 I need a 1 placed in column D

Criteria 2
If any of the column data contains a value greater than .5 but less than 1.0, I need a 1 placed in Column E. I tried using an IF/ Count If statement, but cant seem to get it to return the result I need.

View 5 Replies View Related

COUNTIF With Two Conditions

Feb 11, 2010

I am trying to create a COUNTIF formula which will work with two conditions. If you see the attached spreadsheet you will find the data that I am trying to apply a formula to. I have my data in the table on the right. The table on the left is supposed to show the number which the number of destinations that had a certain range of visitors.

As you will see that there are 3 destinations that had 12 or more million visitors, this was counted using a basic COUNT IF formula but for the rest of my data how can I apply the formula so that the correct number of destinations are counted. For example what formula would be needed to count the number of destinations that have had 8-11.9 million visitors. I am guessing that the formula will have the conditions ">=8" and "<=11.9"?

View 3 Replies View Related

Countif Nested Conditions

Aug 28, 2007

I just need it to be able to test two seperate conditions on two seperate columns but i cant work out how its done

for example, i have two columns on a sheet, one called type and one area, both are text, i need to count how many items of one type are in a particular area. so if i have two columns "area" and "type" i want a total count of how many rows contain area="north" and type="b"

View 13 Replies View Related

Countif Multiple Conditions....

Nov 16, 2008

I have a table like this:

Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...

I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).

It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).

I tried first a simple double conditional to know if a date is inside two dates but it didn't work:

=SUMPRODUCT(--(B:B<J1),--(C:C>J1))

View 9 Replies View Related

Countif With Multiple Conditions?

Apr 14, 2009

In my Sheet "List" I have list of persons working on different projects.
I prepared graph after putting conditions on Project Type, Project Size, Project Year & Position (PM Project Manger). Every thing was done a in a nice manner with the help of below formula.

PJ TYPE , PJSIZE, PJYEAR, POSITION are ranges names.

=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"))

But the problem was occured that in a year if a person work on small project more than once then he will be counted only once. But if he has worked in same year on Medium or Larage project then they will be counted separately. I tried to oversome the problem with the help of Pivot Table and put manually some legend P1, P2 & P3 against the person name if he is working on same type of project in same year. then count only P1 in my formula to count how many Project Manager worked on Project. like

=SUMPRODUCT(--(PJTYPE=$A$2),--(PJSIZE=$A6),--(PJYEAR=B$4),--(POSITION="PM"),--(PMCOUNT="P1"))

Now i am trying that in a separate columm of # of PM there must be a formula which only put P or 1 for a person if he is working on same project in a year but i want that p or 1 only appear against his first entry i duplicate. for other persons it automatically enter 1 or p if they are appearing only once. i have tried a lot while using countif with multiple conditions but all in vain.

View 3 Replies View Related

Simulating CountIf With 2 Conditions?

Apr 23, 2013

I've come across this page around simulating a countif with two conditions. Instead of two 'equal to' statements though, one is a 'less than' statement, and something does not seem to be right - I am not sure if I am getting the syntax wrong but am currently just getting #VALUE! across all cells I try and use.

Currently I have;

Code:
=SUM(IF(Data!$T:$T=$E3,IF(Data!$V:$V&"

View 8 Replies View Related

Countif Both Conditions In A Row Of 2 Cells Exists

Jul 15, 2006

example of what I'm trying to do:
"if the number in G6 is <800 and H6 is >=20 then count the row as 1"

View 9 Replies View Related

COUNTIF Formulas To Calculate Different Conditions

Apr 2, 2008

I am using =COUNTIF formulas to calculate different conditions. such as, calculating how much of one product do we have and how much is is received under 30 days. i want to combine those formulas in one cell to calculate two conditions in different columns.

1-So first i found out the date difference. I used =DATEDIF(Sheet1!C10,Sheet1!E10,"d")

2-Then I found out how many of one type do we carry, using the =COUNTIF.
=COUNTIF(Sheet1!J2:J3810,"DAZ")

3-That was easy, But now i have to sepereate them into 30 or less, 60 days or less, 90 days or less.
Then i used =COUNTIF(D2:D3810,"

View 9 Replies View Related

Excel 2010 :: Countif For Given Month And With Given 2 Conditions

Mar 20, 2014

MS Excel Version : 2010
Attached File name : countif for 2 conditons a given month.xlsx

I want to a count with given 2 conditions AA11 / BB22 and xxx / yyy according to the month in yellow cells.

View 2 Replies View Related

COUNTIF On Two Columns

Jan 27, 2009

I've been asked to extend the counting capabilities of my interface to pick up the profession of each person when they answer whatever is selected in the 'Interface' worksheet (cell B2) put them in my interface.

I've been trying various incarnations of SUMPRODUCT along with what DonkeyOte helped me with before, but currently no joy. Hope that makes sense - have a look at previous post for further details. I've attached an example to look at.

View 2 Replies View Related

Countif Different Columns

Jan 3, 2009

I have 16 columns with 10 rows with different single digits in them. I want to count the number of times the number 2 appears in columns A, C, E, G, I, K, M, O (in other words every other column in this case).

I know how to write the formula by using countif to find the results but it is rather long. The fomrula would look like this:

View 5 Replies View Related

How COUNTIF For Both Columns

Oct 7, 2008

I have two columns and have to count all "DATA PAPA" rows

How i can do it?

View 10 Replies View Related

Countif With Three Columns

Mar 30, 2009

I am using this formula

=COUNTIF($C$1:$C$4000,B1)

How can I add a third column D to the formula to check if the value in 'D'
is the same

eg.
A B C D
1-Jan 1-Jan 4523
2-Jan 4523
3-Jan 4523
4-Jan 4501

View 9 Replies View Related

Countif But Columns Not Adjacent

Jan 8, 2009

I have a spreadsheet where I need to count the # of times the value 1 is in a row, but the columns that I need to check is every other column.

View 3 Replies View Related

COUNTIF For Multiple Columns

Mar 19, 2009

I am trying to tally answers for a survey. Column A specifies one of 3 locations (Boulder, Larimer, Westminster) and column C specifies a grade for services between locations (Not Uniform, Slightly Uniform, Very Uniform). I was able to tally each separately using 'COUNTIF' and the conditions, but now I would like a total for each of the grades by location, i.e. a count of people answering both Boulder and Uniform, etc.

View 5 Replies View Related

Getting A COUNTIF Based On Two Columns

Jan 6, 2009

I need to have cell with the total rows of Column E that equals TODAY() AND Column F = "Repair". I have tried various COUNTIFs with not much luck.

View 5 Replies View Related

How To Countif Columns Of Dates

Aug 10, 2009

I have three seperate Formulas on a summary page that will count dates depending on whether they will be approaching in the next month, whether todays date falls within them and if they are expired or are less than todays date.

Basically the form was developed to monitor a maintanence schedule and give a summary to the person in charge of cleaning so they can forecast there work load. I have managed to get the formula working for "whether todays date falls within them" ("# of Computers than can be cleaned today" as it appears on the summary form) but I cannot seem to get the other formulas figured out.. Well at least the formula that checks to see which computers are past due.

As far as the formula that checks to see which computers will need to be cleaned within the next month, I don't know how to make that work at all considering the setup of my Date input page.

View 10 Replies View Related

CountIf Blank With Two Columns

Feb 25, 2010

counting cells if blank. I have two columns E and J . For example if E5 and J5 are both blank I want that to count as 1.

View 13 Replies View Related

COUNTIF On Multiple Columns

Jun 6, 2009

When using the following:

=COUNTIF(F6:F200,"G")
I get what I want.
What I can't figure out is, I want to get the count of columns G, J, N, and R which has the criterior that I'm looking for, added together for a total.

And in reality, the rows do not go down that far, but they might, so I put 200 in there to be safe.

View 9 Replies View Related

Sum 2 Columns With 2 Conditions

Mar 22, 2014

SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))

place the amount from table column "IN" into a corresponding cell in another sheet. how to add another column (OUT) in the above formula. I wish to summarize both column IN and OUT from table based on two conditions.

Workbook example: [URL]

View 3 Replies View Related

CountIF Combine The Results From Two Columns

Feb 15, 2009

I have two columns A and B. Column A contains 'Machine Nr' and column B contain 'Week Nr'. In cell C1 i want to write a function so that it counts the number of weeks in Column B where week number is 4 AND the machine nr is 1 in column nr A.

Column A Column B
Machine Nr Week Nr
1 4
1 4
2 4
2 4
3 4
3 4

View 10 Replies View Related

Countif With 2 Columns Of Data And No Duplicates

Dec 26, 2012

I would like to use the sumproduct or countif formula for a set of data. I have looked around the forum and have not found what I need so

Column A______Column B
West___________1
West___________2
West___________1
West___________2
South__________ 1
South__________2
South__________1
South__________2

I want to count column B for all "West" (column A) and I don't want duplicates. So it would count two unique characters for West and two unique characters for South. I want "west" and "south" separated.

View 3 Replies View Related

Array Formula Using Two Columns And Countif

May 11, 2006

Having trouble getting Excel to compute a value based on two columns on another sheet. What I need it to do is see if column A has the value I specify, then look in column B and see if that date figure is greater than 180 days, and then count it. So I might have 45 that meet criteria A, but then need to find items in that subset that meet criteria B...

View 9 Replies View Related

Only Countif Cells Adjacent To Columns Contains A Blank?

Apr 22, 2014

I have a formula (below) where I want it to count the cells that contain a date that is less than or equal to today, but only when the adjacent cell is blank. The latter part of the formula works, but I'm struggling to tell it to only do the count when cells in column I are blank;

=IF('QS12'!I10:I3120="",COUNTIF('QS12'!H10:H3120,">="&TODAY()))

View 9 Replies View Related

SUMIF Based On 2 Conditions From 2 Columns

Jul 16, 2008

Have had a search around the web forums for an answer to this and am not getting anywhere.

In column A i have text - apr, may, jun etc
In column E i have general format text - a 6 figure number
in column M i have text - eg 886.97 (a currency, but in text format)

i want to:

where column a=apr and column E=212130 sum the contents in column M

View 9 Replies View Related

Averaging Values In Multiple Columns With Conditions

Dec 18, 2013

I am a teacher creating a grade book in Excel. Each student is in a single row, with columns for the various assignments. For data analysis purposes, I am trying to develop formulas to make two different calculations.

The first calculation is simply the average quiz grade for all students, for all quizzes.

The second calculation is the average quiz grade for all grades that are higher than zero. This will tell me the average quiz score for quizzes that have actually been attempted.

I do need to track students that have withdrawn from the class, so column A will contain the letter "w" if the student has withdrawn from the class. If the student has withdrawn, the grades for that student should be ignored in the calculations.

Since I do accept quizzes after their due dates, a particular quiz score will have an entry of "L" if it has not yet been taken. These entries also need to be ignored in the calculations.

See the attached workbook for some sample data and the expected results.

Sample Workbook.xlsx‎

View 4 Replies View Related

Make Columns Visible/invisible As Per The Conditions

Sep 4, 2009

I am trying to create a daily task activity sheet. I would like to know, how to make the columns visible and invisible as per the selection from previous column.

For eg:

Column A has a list which has values 'TC creation' , 'TC modification', 'System Test' etc.

Upon selecting TC creation, i need to make a column visible ie 'total tcs created'.

Upon selecting 'TC modification' i would have to make the previous column invisible and make a new column visible for entering no of tcs modified.

Upon selecting 'System test' the other columns should go invisible and make other 3 columns visible for no of tcs executed, no of tcs passed, no of tcs failed etc...

View 12 Replies View Related

Reading 2 Columns And Extracting Data Under Certain Conditions

Oct 2, 2011

I have two columns. In one people either saw Red, Blue, or Green. In the other people either chose One, Two, or Three.

I would like to get excel to read the first column, find all the times someone picked red, then look to the column next to it and see if they picked the number One. Then I would like it to count that situation. Then I want to do it for Two and Three. In the end I would have something that looked like this


---------One----Two---Three
Red-------5------8-------9
.
Blue-------4------5-------3
.
Green-----4------2-------6


Generated from something that looks like this

Red One
Blue Two
Blue Three
Green Two

Some kind of biconditional countif maybe

View 1 Replies View Related







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