Free Microsoft Excel 2013 Quick Reference

Count If - Multiple columns

I have a spreadsheet and there's a column with open positions and filled positions. Each row; whether filled or not, has a grade assigned. I'd like to keep a running total of how many positions I have filled and opened at each grade.

Column B is the "Open" column. Column D is the "Grade" column. Column H is the "Filled" column. Essentially, I would like column D to break out each grade individually that is open and Column H to break out those that have been filled.

This is my thought process but I'm not sure how the formula would be written:

Total of open Grade I positions: If Column D="I" and Column H<=0, then count

Reverse for total of filled Grade I positions: If Column H=1 and Column D-"I" then count

Sample attached. Thanks!
Example CountIf.xlsx


Post your answer or comment

comments powered by Disqus
Hi,

I would like to countIf the conditions of multiple columns exist..
CountIf(A:A,"RS" and the respective row in column "H" = "Sent to" but the
same row in column "J" must be blank.)

Help please.
I've spent an hour trying to think how to put this without putting to much
detail and making it to complicated.

Thanks Dean

Hello, I am having difficulty being able to Count Occurences where multiple criteria are met across multiple columns.

For example, in column C, I have "True" and "False" Values, and in column H I have "True" and "False" Values. I would like to be able to count the number of occurences where "True" is found in the same row, in both columns.

THe formula I had been using in the past does not seem to be working:

=COUNT((IF('Raw Data'!C$4:C$504="TRUE",IF('Raw Data'!H$4:H$504="True","",'Raw Data'!H$4:H$504))))

Thanks!

Jacob

I have put together a calendar for scheduling work and I want to add up the number of times person A works on project B. Since it is in calendar form I have to count from multiple columns. How do I do multiple columns?

I have used =SUMPRODUCT(--(D7:D40=$D$42)*(E7:E40=$E$41))
and =SUM(IF(G7:G40=$G42,IF(H7:H40=E$41,1,0),0)) which work for counting single columns but I can't figure out how to add multiple columns.
d42, e41, g42 are names

Hello

I need to keep a track of the number of returns we get from a customer survey each week. I currently use this formula:

(COUNTIF('Survey data'!A:A,A8))

A8 always equals the current week number.

I've been asked to add a filter the survey questions. I've managed to filter all questions, but the actual number of returns is confounding me!

The filter is in cell B1, if call B1 is blank, no filter has been applied. The team name is column W of the survey data.

Can anyone help, i've tried messing about with COUNT IF, Sum* etc .

Thanks

I wish to know the best way of getting a result for the following :-

I have a column of names on sheet A Column A.
I want to count the number of times the name occurs on Sheet B Column
A...BUT also need the following extra condition...Only count if Sheet B
Column B >0.

Thanks for your help in advance !!!

I am using Microsoft Excel 2003.

What I am trying to do is count values in column g (as long as greater than
0), if a specific value is held in column e

Both formula work in their own right, but when I put them together, it is
missing out on counting only if the value is greater in g

=(COUNTIF($E$3:$E$37,$F68))*AND(COUNTIF(G$3:G$37," >0"))

Can anyone tell me where I'm going wrong

I wish to know the best way of getting a result for the following :-

I have a column of names on sheet A Column A.
I want to count the number of times the name occurs on Sheet B Column
A...BUT also need the following extra condition...Only count if Sheet B
Column B >0.

Thanks for your help in advance !!!

How can I count two columns with different criterias. In one column if it
contains 737 and if another column contains LAX, how can I count them if they
only meet this criteria? Thanks for the help.

I have a situation for the experts here... I have 2 columns, they both consist of dates (hopefully makes things easier), 1 of which has dates that are forecasted... the other column has SOME dates and some blanks... now if the first column, lets say column J the one with the dates.. IF that forecasted date is >= 2 days I need the cell matching the row of that date in column 'G' to change color to red... can this be accomplished without vba/macro?? So far I have used:

	VB:
	
="&TODAY()-2,$G$5:$G$807,"") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
For the formula to count if those columns do or don't have a date included from 'today's date (I know this needs modified)

now in case others place or paste dates into the J/G that are not consistent, I used:


	VB:
	
=CONCATENATE(TEXT(J5, "mm/dd/yyyy")) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am just unsure how I can get this accomplished the most efficient way. Thanks to anyone who may be able to help with this....

Best Regards,
Jon

I have a column that I need to sum if multiple columns meet a certain criteria.

The three columns are: Region, Period, & Restaurant. The column that I want to sum is Units of Hamburgers.

Basically I need to sum the units of Hamburgers sold in a spreadsheet. The trick is though that some of my line items might repeat. For example, in my spreadsheet I might have data that looks something like this:
Col A | Col B | Col C | Col D
1 USA | 2008Q3 | McDonald's | 25
2 USA | 2008Q3 | Wendy's | 14
3 USA | 2008Q3 | Burger King | 14
4 USA | 2008Q3 | McDonald's | 14
5 USA | 2008Q3 | Wendy's | 29

I could create a separate column where I create a key: =A1&B1&C1

and then create my sumif like this: "=sumif(D:D, "USA2008Q3McDonald's",C:C)

I don't really want to create the additional column D though with the key formula. Is there a way I can do the sumif without adding a key. I thought perhaps of using an array formula or something, but I've never done this before.

Thanks in advance.

I'm not sure if this is a count if problem but we have gender in one column, M or F and we want to count the Met, Exceed or Did not meet in another column that corresponds to the gender. A formula like if(Gender column=M, count if (Score column, Met)) does not work.
HELP! we need to get this done quickly. We just need to know how many males/females met, exceeded or did not meet.
Male E
Male D
Male M
Female M
Female E
Female D
Male M
Female M

I'm trying to count cells if muplite variable exist. For example, I want to
count if a range of cells is >0 or

I’m trying to add totals of multiple columns (months) that are conditional. Each Month has two columns assigned to it ie 24 columns in total. For each month there is a column that can be set to any value of A,B,C,D,E or F and the second column can be set to any value of Y, N, or tbc. I need to report for each month how many occurrences of ‘A’ there are when the second column is ‘Y’, how many occurrences of B there are when the second column is ‘Y’, etc. I’m guessing SUMPRODUCT would be the most efficient way to do this but can’t get it to work?

Hello...

Could someone please tell me the easiest way to basically do the
following?

"If Cell A1 and Cell B1 both contain something, then count... If both,
or one of the Cells is blank, then ignore."

So basically...
A1 B1
x x
x
x
------------

I would like my formula cell to return a value of 1...

Thanks for any help!!!!

Stacy

~Hello. I'm adding a dashboard page to a complex tracking worksheet (macro-enabled, shared and protected Excel 2007). Where I'm stuck is trying to autopopulate a row showing a count of the number of dates in a ReviewDate column that fall between certain dates only if multiple criteria are met. I've attached a sample spreadsheet and mocked up someting below.

Sample excel file
Dash.xlsm

The results will be on a dashboard page needs to have nicer (brand standard) formatting for senior leaders, which makes Pivot Tables difficult to use. The spreadsheet is not that large so calc times are not a concern (yet). I've tried COUNTIF, COUNTIFS, SUMPRODUCT and everything else I could think of and have struck out. I think my main problem is the stringing the complex syntax together properly.

Your expertise and suggestions are appreciated! If I can solve for #1 below, I can figure out the rest I think. Thank you!

Example of source data

ReviewDate Status Approach LOB 7/23/2011 Not Started ILT A Finance 7/25/2011 In Progress ILT B Finance 7/26/2011 Develop WBT A Tech 7/23/2011 Not Started WBT B Finance 7/25/2011 Complete WBT B Finance 7/26/2011 Complete ILT B Finance 7/29/2011 Develop ILT B HR 7/31/2011 In Progress ILT A Finance 8/2/2011 Not Started WBT A Finance

What I'm trying to figure out is:ILTs Due = COUNT of Approach "ILT A" OR "ILT B" where LOB = "Finance" AND Status = "In Progress" OR "Not Started" AND ReviewDate is between July 11-18, 18-24, 25-31, etc.ILTs Complete = ILT A or ILT B = "Complete" and LOB = "Finance" (just a simple running total)

Example of dashboard table
[Finance]
Week of July 11 July 18 July 25 August 1 August 8 ILTs Due 2 2 1 ILTs Complete 1 WBTs Due 1 WBTs Complete 1

Hi,

I need to present metrics on the project i am working on.
My spreadsheet contains a list of multiple user features in column A grouped by a prefix e.g "AUD_", "BTV_", "TUN_" etc. I need to find and count all rows that begin with "TUN_" and that meet criteria column D = "requirement" and column E = "Approved"

I have tried for days with COUNTIF, SUM(IF(...)), DCOUNTA, SUMPROCUCT.

Please Help!

Thanks

Mark

Hi, Hoping someone can help. Please bear in mind that I know very little about Excel - Infact so little I'm surprised I can spell it.

I would like to count data over 5 columns so that I know how many times the same thing has been entered.

I can get it working for ONE column but not over multiple columns. EG:
I have column O titled 'count' this has a formula in each cell as follows: =SUM(COUNTIF($E$9:$E$31,$E$9:$E$31))

I then have conditional formatting set on the column so that when a value in column O is 5 it turns red. Alerting me to the info i need.

This is great and works and shows me a count of repeated data in column E between cells E9 and E31.

What i want to know is, IS it possible to do a similar thing so that it returns the count for columns E, G, I, K & M within the same range. (9 to 31)

Say I type in TEST in column E and TEST in column K -In column O the data would be 2. as i have entered TEST twice.

Hope that makes some kind of sense

Be great if someone can help or point me in the right direction.

Many Thanks

I've attached an example.

How do I create a countif formula for multiple
conditions? I have a spreadsheet with two
colums: "agents name" and "rental or sale". I want to
create a formula that will count if the agents name
= "Linda" in column 1 and "rental" in column 2.

Any ideas?

Hello,

I am having a great deal of difficulty with a formulas. I need a formula
that will count across multiple columns and rows. For example, based on the
info below, if I wanted to only know what the count was for "NEG" from column
A, plus "CHI" from column B, plus "CITY" from columns C and D. Based on the
below criteria the answer should be "3." Can anyone please help. Cheers.

A B C D
1 NEG NYC CITY CITY
2 NEG CHI STATE CITY
3 POS LA TOWN PARK
4 NEU DC VILLAGE OCEAN
5 POS DC HOME APARTMENT
6 NEG CHI CITY CITY

I have a sheet where a name could appear multiple times in a 22 column data range. I would like to list all the names on a second sheet in Column A and in column B (and further columns as necessary) return the heading name of column where the name is found in cells next to the name. I think Count If will work for giving me the number of times the name appears in sheet one but is there a way to return the column headings along the same row as the name?

so for example - column "D" has 582 rows and each cell has 15 different values (1 to 9 and A to F) so i need to select all the instances of say the "C" of the 582 - now the tricky part i need to then have it tell me for all of the "C" values what is the "countif" total in the "M" column for only the "C" values. there are four possible values to count in the "M" column. if needed i can upload a sample of the sheet. also if it is something easy to duplicate for needing it multiple instances (15 of them like 5 times each) selecting the columns and then replacing the criteria would be best. sorry for the lack of terminology. anyone that can help i would appreciate it more than words can even begin to explain. Thank you. Abel.

PS the entire title did not fit :
sort and report multiple criteria from multiple columns and report back a count to one cell the # of entries

I thought this would be pretty simple for me but I am not able to pull the formulas together. I have dates in column A and a corresponding height in Column C. I am trying to look up by month and year in column a and then count if there is a non zero number in column c of the same row. I want to be able to add how many values there are for each month of the year and then place that total sum for that month/year in another cell.

I have tried the vlookup, counta, sum, offset, match but I can't get the right nested formulas to work together. I think I may be making this more difficult than it needs to be. I've seen some formulas when searching but they don't seem to count, they always sum or do something different with the values. Any help? See example spreadsheet.

I am trying to run a count if formula that needs to meet multiple criteria.

For example:

Column A - Males or Females

Column B - Ethnic Background

Column C - Termination Reason

Column D - Age

Can I run a formula that will go through the entire list and tell me all of the White males that were terminated for insubordination at age 24? Or better yet all of that criteria for anyone under the age of 30?

Thank you in advance for any help.

hi there,

im a new poster in new of some help, im working in excel 2003. Im try to create a formula that counts cells, on a particular sheet (Col Data - National) if there is "2007" in column B AND a "Y" in Column E. i have come up with the formula below but its throwing back a "0" result when i know theres 12 in there, can anyone spot where im going wrong, its the first time i have tried creating formula with multiple crteria??

=COUNT(IF('COL Data - National'!B3:B315="2007",AND('COL Data - National'!E3:E315="Y")))

cheers!!


No luck finding an answer? You could always try Google.