Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Countif and between two numbers Results

Hi,
How can I have two conditions in my COUNTIF?

For example the number of cells in Col A which are between 5 & 10?

CountIf(A1:A100; And(?? ; ??))

Thanks
Payam

Try combining two countif functions, one to count all entries >= 1300, and
another to count all those >=1400. Your answer is the difference between the
two:
=countif(range,">=1300")-counitf(range,">=1400")
--Bruce

"Stu Leslie" wrote:

> Using Excell 2000, I have a column with time as a 4 digit number from a 24
> hour clock and I need to count all the times into their respective hour cells
> - eg: 1315 hrs needs to be counted in the 1300 to 1359 hrs cell. I have tried
> Countif "> 1259 and < 1400" but always get O, irrespective of how many 4
> digits numbers I put that fall into that 1259 -> 1300 catergory. As a bit of
> a novice with Excel, I suspect I'm looking for a "way too easy solution" to
> my formula and will probably have to get much more technical.
> Any suggestions greatly and gratefully accepted as this one has had my head
> scratched for a while now. Thank you for your > time so that I can use < of
> mine.

I have a column of dates where I need to count the number of instances that
fall within Jan 1 to Jan 31 2007. I have tried to use the countif function
and can only count values greater than a value or less than a value, but not
between two values. Is there another way?
--
Linda W

I need to count a number of entries that are between two dates where that
data is stored in column A and also if the text value in column F is "yes".
I have a formula to count the entries between two dates and a formula to
count the entries for the text equal to "yes", but I cannot bring it together
to do both...any suggestions?

=COUNTIF('Student Listing'!$A$2:$A$300,">=10/1/04")-COUNTIF('Student
Listing'!$A$2:$A$300,">=10/31/04")

=COUNTIF('Student Listing'!$F$2:$F$300,"Yes")

Thanks!

Hi,

I'm trying to count a specific range of values that occurs between two
number in column. I tried some countif and IF functions but couldn't land the
right formula.
Any help is really appreciated.

Here's my example:

In a single column from A2:A200 there's a set of numbers with values ranging
between 1-200.
I'm trying to find a 'count' of all numbers in this column that fall
'between' 101-140.

thanks for your assistance,
_kenm

--
kenm

Try combining two countif functions, one to count all entries >= 1300, and
another to count all those >=1400. Your answer is the difference between the
two:
=countif(range,">=1300")-counitf(range,">=1400")
--Bruce

"Stu Leslie" wrote:

> Using Excell 2000, I have a column with time as a 4 digit number from a 24
> hour clock and I need to count all the times into their respective hour cells
> - eg: 1315 hrs needs to be counted in the 1300 to 1359 hrs cell. I have tried
> Countif "> 1259 and < 1400" but always get O, irrespective of how many 4
> digits numbers I put that fall into that 1259 -> 1300 catergory. As a bit of
> a novice with Excel, I suspect I'm looking for a "way too easy solution" to
> my formula and will probably have to get much more technical.
> Any suggestions greatly and gratefully accepted as this one has had my head
> scratched for a while now. Thank you for your > time so that I can use < of
> mine.

How do i count the number of cells in a column containing "offer withdrawn". That have actually been withdrawn between two dates. The date is shoun in another column.

The AND function doesn't appear to work, when used within the COUNTIF function. im stuck now, can anyone help??

This formula counts "offer withdrawn" in column g.

=COUNTIF('Clearence Status sheet'!G:G,"Offer Withdrawn")

ive tried this: but it didnt work

=COUNTIF('Clearence Status sheet'!G:G,AND("Offer Withdrawn",COUNTIF('Clearence Status sheet'!L:L,">=38108")-COUNTIF('Clearence Status sheet'!L:L,">38138")))

NOTE: the 38108 and 38138 should count all dates in May 2004.

Can anyone help??

Many thanks

Rob

I have two questions.

1) I have to count the number of times a certain value is shown in a group of columns, but it has to be every other row (even/odd), for example:

Material
prod A prod B

Name 3
2 1
Name 2
5 5

It is made more complicated by the fact that the above values won't really be numbers, but miniature pie representations of values.

The problem is that there are no blanks between the top value (Material) and the bottom values (products), so I can't use CountA. Also, a criteria needs to be made with Countif. I was hoping to figure out how to use countif to calculate every other row. The only way I know of it with =COUNTIF(G6:G6:G8:G8:G10:G10, 1), but it takes forever to write up (I can't just point & click).

The only alternative, if I can't find a way, is to bring all of the materials to their own columns of as an additional tab on a report, as well as bring each product of the materials on as columns of their own. This will look very messy and time consuming (there's around 30 materials, each of which has about 5 products, so in total about new 150 columns would have to be made).

2) Luckily, the Material value is only on the first cell (it is merged across 5 cells), so the last four products have blanks between them, so I can theoretically use the counta function for those four products, however when I try to combine the countif and counta functions (criteria still has to be met), like this =countif(counta(g6:g16), 1), I get an error.

Any help?

I am working in a workbook where the formulas reference between two worksheets. I am trying to count the number of candidates that are applying for the same job requisition number and are in active status. The job requisition number can be found in column A and the Active or Inactive Status can be found in column J. Here is the formula that I am using....

=COUNT(IF(('Candidate Summary'!$J$3:'Candidate Summary'!$J$77),"Active", IF(('Candidate Summary'!$A$3:'Candidate Summary'!$A$77),'Current Opening Summary'!A3)))

However, it just returns a 0 value.

Anyone have any guidance?

I am trying to figure out how to search a table for information between certain dates. In my attached file, I need to find all 4 user’s Total Score, Number of Scores, and Score Average. I cannot figure out what formula will allow me to do a search for both the user by name and the evaluation by date, and return a sum or average. I am unsure how to do a countif and sumif when searching for two different variables (user name and evaluation date). For example purposes, we can say that we want to find the necessary information for all users between the dates of 1/5 and 1/7. Can anyone help me with this one? Shoudl a pivot table be used here?

I'm using editgrid which is basically excel on the web. I'm a total newbie to excel and would like to understand the following from start to finish:

I'm trying to count the number of rows in a spreadsheet between two given ranges that are between a specific date. To me, logically this would be done like =countif(Ax:Ay, date), but this doesn't work obviously. I've been searching around the web and found a fair amount of pages suggesting you use SUMPRODUCT to count rows when you have a date-range as range(if that makes sense). I just CAN'T understand why, or how that practically works. It's a real simple spreadsheet I don't need the fanciest formula, it's basically to keep track of sales, and if these sales are within the given range(which is weekly) then I want to show how many sales have been in a given week.

Can someone explain this comprehensibly to a complete newbie? I've tried varies today() stuff and other &date(x) stuff but it doesn't do anything good for me.

Any help is so very very appreciated and I hope you all have a wondrous happy day!

Hi, I'm sure my answer is somewhere in these gazillions of threads but I can't find it! I really need to know how to have Excel look at a date (mmddyy) in cell G7and determine if that date, plus one, is more than or equal to the date in B3 and is also less than the date in B4. If the date in G7 meets both criteria, then I need to count that date. Then of course, I need to copy this formula down the page for G8, G9, G10.... The formulas should always refer back to B3 (which is the start date of the current billing cycle) and B4 (which is the last date of the current billing cycle) however. What I'm attempting to do is to count the number of admissions on a bill that occur in the current billing month. G7,G8, G9....are individual admission dates which could be dates from previous months; i.e. I'm doing the June bill and the admission date in G7 is hypothetically 05-31-09. My bill start date is one day after the admission date (hence the "plus one" mentioned previously), so this date should be counted. However, let's say G8's date is 04-03-09. so G8 should not be counted. If G9's date is 06-22-09, it also needs to be counted. Hence, if that was my entire bill, I should have two dates that were counted. Is this a CountIf function? I tried making another column and converted all the admission dates into Julian dates, and made separate cells for the Julian dates for B3 and B4, but still couldn't get a formula to work.

Thanks for any help!

misspen

I have a spreadsheet that has dates in the left column A2:A100.

Columns to the right have numbers that I wish to count the number of times particular numbers appear.

COUNTIF works fine, but I would like to use COUNTIF for rows that are between two specific dates.

I have used LOOKUP to find out the specific rows between which I want to COUNTIF...which works fine.

The problem is how do I take the results from the LOOKUP results and get them to automatically change the range in the COUNTIF formulas.

Thanks in advance.

I'm a little stumped on what equation to use for this. I have a spreadsheet that we use to track quoting activity for business. It shows us ROI, close ratio, etc. One thing I can't find out is how to track quote activity by month or even week.

Ex. I have one sheet of multiple entries with dates, ex.

---------A---------B-------------C
1---03/01/2012---jane doe-----quote #1223
2---04/05/2012---John Doe-----Quote #234
3---05/06/2012---James Doe----Quote #4432
4---05/07/2012---Larry Meyers---Quote 342

What formula can I use to count the number of entries within a range of dates and have them laid out like this.

----------A-------------B
10---March, 2012--------1
11---April, 2012----------1
12---May, 2012----------2

I tried using -countifs(A1:A4,<A10,A1:A4,>A11) But it doesn't seem to work.

I appreciate any help. Thanks!

I have three cells, each of which can contain a number which is a train unit number. A train can have between one and three units and those units are made up of varying numbers of coaches depending on the unit type viz
unit one unit two unit three coaches
train 1 100101 4 (units numbered between 100000 and 199999 have 4 coaches)
train 2 200224 200193 10 (units numbered between 200000 and 299999 have 5 coaches)
train 3 100322 100084 100932 12
train 4 200021 103984 9 (units numbered 100 and 200 series can be coupled together but only one of each as no train can be longer than 12 coaches)
train 5 300343 302023 6 (units numbered between 300000 and 399999 have 3 coaches and can only couple to other 300 series units)

I can get Countif to tell me how many units are in each train but is there a way to get Countif to calculate the number of coaches in the train? A train has between 1 and 3 units but no train is longer than 12 coaches (so there can't be 3 200 series units in a train)

My spreadsheet will have several trains to a page and each row will have additional data but my problem is calculating the number of coaches automatically from the 1-3 unit numbers entered. I'll be eternally grateful is someone can show me the formula for this calculation

Orly

I'm new to the forum and relatively new to functions/formulas in Excel. With that said, I need some help creating the correct formula in Excel. I am trying to find the number of hours between two dates excluding weekends (Saturday & Sunday). The point of the calculation is to count the number of work orders that were resolved in less than 2 working days or 4 working days, so I have been using a 24 hour clock for the formulas I was working with.

I have searched the forum and this is the closest thread I have found, but the numbers don't come out right.

http://www.excelforum.com/excel-work...-weekends.html

I tried this formula:

=(NETWORKDAYS(A2,B2,F$2:F$28)-1)*(E$2-D$2)+IF(NETWORKDAYS(B2,B2,F$2:F$28),MEDIAN(MOD(B2,1),D$2,E$2),E$2)-MEDIAN(NETWORKDAYS(A2,A2,F$2:F$28)*MOD(A2,1),D$2,E$2)

I butchered the formula and came up with this:

=(NETWORKDAYS(E2,F2)-1)+IF(NETWORKDAYS(F2,F2),MEDIAN(MOD(F2,1)))-MEDIAN(NETWORKDAYS(E2,E2)*MOD(E2,1))

This is the closest I have come up with on my own, but the counts are still off versus a manual count:

=COUNTIFS(P:P,"<=48:00:00",I:I,"Nectech",C:C,"closed",P:P,"<96:00:00",Q:Q,"<=3")+COUNTIFS(P:P,"<=48: 00:00",P:P,"<96:00:00",I:I,"Forscomtechs",Q:Q,"<=3",C:C,"closed")

P:P refers to the hh:mm:ss difference between the date opened and resolved in columns E & F (E, Date Opened, 1/6/2011 19:55, F, Date Resolved, 1/10/2011 17:22)

I:I refers to the work group name.

Q:Q is the number from NETWORKDAYS, which has thrown me off because it counts any part of the day as a full workday.

C:C is the status of the work order. I included that because originally the count was including work orders that were still open.

Any help getting an accurate time difference excluding weekends between the date opened and resolved would be greatly appreciated! I can figure out the formulas to count them up if I can figure out how to get the time difference right.

Thank you all in advance!

Problem:

Finding the number of values in List1 (Column A) that are larger than 20 and smaller than 50.

Solution:

Using the following COUNTIF formula:
=COUNTIF(A2:A7,"">20"")-COUNTIF(A2:A7,"">=50"")
Or this SUMPRODUCT formula:
=SUMPRODUCT((A2:A7>20)*(A2:A7

List1
30
10
60
40
15
55

Result 2

Hello All,

I have a pretty complicated Excel problem that I've been trying to solve using array formulas without success. I am a total novice at VBA and so ideally a formula approach would suit me best to fix this but I'm willing to try anything!

I have a set of data from an experiment which consists of a column of sizes and another with a measurement. I have managed to get the size data split into bins using the following array formula:

=FREQUENCY(size,I3:I52)

What I really want is to be able to have another set of bins with the measurement in and then only count the size of the measurements of a given value. Well... from here it went a bit crazy! I tried many things with varying success (please bear in mind that I was trying most of these for the first time):

Dcount - just gave total count in all bins
Countif (AND) - was hoping it would only count the size if the measurement was between two values but couldn't get the syntax right to even see if it was right!
Count(if(and( - Same goal as above but again the sytax wasn't good for me

All of these still involve me manually assigning the measurement bins and then using a round about way of saying if between these to numbers. I
Basically it wouild be nice to be able to just have =FREQUENCY(size,I3:I52)AND(measurement,K3:z3) obviously wrong syntax but I hope it gets the point across. The overall aim is to have the number of each measurement value for each size and then plot this on a contour plot. The instrument that does this at the moment does it all in MSDOS and so I'm trying for an update.

I look forward to any advice you can give me

Cheers

Jarvice

Hi guys

I have the following formula to calculate whether an item has been completed between two dates (the user enters the start date in Instructions!G7, and the end date in Instructions!G9).

So each item has a completion date in column AS of the RP Report sheet.

My problem now is to get a count of items completed Year-to-date. I can't put an extra column in the RP Report worksheet, so was just wondering if there was a way to use a COUNTIFS formula to count the number of items completed where the year of the completed date equals the year in the date input in Instructions!G7.

So a sort of extraction of dates?

This is wrong but kind of what I want:



Hi there -

I'm using Excel 2007, and I've been trying to figure a problem out now for a few days. I've tried several suggestions found in other threads, but nothing is working out.

I have a table of employee information. There are about 1500 rows with about 25 columns, and it updates automaticaly from a web source each time it's opened. It's sensitive information, so I'd prefer not to post it on a public forum.

Column P has a "start date" and Column Q has an "end date."

What I need to do is figure out a formula that will count the number of active employees we had in a given month for a dashboard. For example, one of the cells needs to return the number of active employees we had for April 2011. The formula should be able to count all the rows that had a start date greater than 1/1/1900, but less than the end date of 5/1/2011.

"Sheet 2" houses the dashboard. Cell A3 has the date: 1/1/1900, and then the columns have the last day of a particular month (So, A4=2/1/2011, B4=3/1/2011, C4=4/1/2011, etc.)

The most common suggestion is to use a SUMPRODUCT formula, like: =SUMPRODUCT--('datasheet'!P:P>=Sheet2!A3)*--('datasheet'!Q:Q<=Sheet2!B4).

I've also tried COUNTIFS formulas with similar expressions, but nothing works! I either get a return value of zero, an error, or some sort of bizarre, completely unbelievable number, like 984 (the most we've ever had active at one time usually hovers around 500.)

Am I overthinking this? Could it be that columns P and Q are unsorted? If yes, is there a workaround?

I would be very grateful for some suggestions. Thanks very much in advance!


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