Free Microsoft Excel 2013 Quick Reference

Sample countifs Results

We collect data every 10 minutes continuously for a month at a time. When a
set trigger value is exceeded, a sample is taken. However, only one sample
can be taken in a 24-hour period (beginning and ending at 2:00 pm). The
information I need is . . . . how many samples would be taken in 1 month at
varying trigger levels (0.75, 0.80, 0.85, etc.)? Ultimately, we would like
to know what trigger level would produce 5 samples per month.

The spreadsheet has the following column headers:

Date Time Value

Any suggestions would be greatly appreciated. If more info in needed,
please let me know.


Dear Friends,

I'm trying to calculate using COUNTIFS and kinda hit a minor problem. If i type manually the criteria, i can get the answer, but if i wish to use cell reference as criteria to indicate a range like 201-220 i'm not sure how to put in the data into a cell.

Attached is the sample of what i intended to do. The ones highlighted in grey (Option 2) are those i can't get the answer as in Option 1.

Thank you.

I have a data set that has long rows of cells that contain any number between 1-5. I need to locate the first occurence in each row whereby the number "2" occurs, but only when the following 17 adjacent cells also contain the number 2 (to make a total of 18 consecutive 1's in a row). I have been very stuck on this. Any help would be greatly appreciated!

I have attached a sample of the type of data I am looking at.



I have a speadsheet in which i have some date in columns A-E


One row represents an order for each address

I have managed to come up with a formula which counts the number times a date within the last xx number of days occurs...

I.e. number of orders in the past 3, 5, 14 etc days

I also have areas, what i would like to do is now come up with a formula which will count the number of orders in the past 3 days for the areas...

i.e. the number of occurrences of a date when column D = xx.
- countif Column B = a date within the last 3 days and Column D = V1

Please see my post on

This seems to be not a million miles away, my problem is my data set includes formated dates.

Would a sample upload help?

Hello all,

I have a problem with my countifs statement in that it is too long. All that I want to do is count how many rows do not have an NA value. The problem is that I want to do this with multiple columns. Attached is a sample of what I am talking about with my countifs statement. I think a sumproduct formula would work here, but I can't seem to figure it out. Any help would be greatly appreciated. Thanks.


Using Excel 2007

In the following example I want to count the number of times a "4" shows up but just for the cells A1:D1 and A3:D3 (non adjacent cells). When I write the formula =countif(A1:D1,A3:D3,4) I receive an error.
See attached sample.

How can I write a countif formula for non adjacent cells?
Any help would be appreciated.



i want to write simple formula = sum(A1-B1) into complicated formula using vlookup & countifs.

I have 3 tables. First one is original values, Second one is the difference after calculation & Third is showing the difference between these 2 tables. I was just wondering that can it be possible to write simple formula into complication formula using countif and vlookup

Sample file is attache for your reference.

Hiya, I am trying to get a COUNTIF formula to work,and clearly I have done something wrong, although I can't see for looking what it is!

I was hoping someone might be able to help me correct my current formula, which I have attached in a sample of my spreadsheet.

Thanks for looking and for any advice you can give me.

Kate :-)

Hey everyone,

To start, I'm less than a novice when it comes to programming & VBA. I'm looking for a macro that can paste a value down a certain number of rows. The number of rows to count down will be determined by the number in a given column. This value will change for certain rows. The number to be pasted will also change for certain rows. Below is a sample of the data. Attached is a sample of the data.

Property, Count, TotalSF, $PSFAdj
7, 8, 132410, 239.78
8, 0, 0,
9, 0, 0,
10, 0, 0,
11, 0, 0,
12, 0, 0,
13, 0, 0,
14, 0, 0,

For example, I'm looking for a macro that can paste the "$PSFAdj" value, 239.78, down according to the number in the "Count" column, 8 rows. There are roughly +860K rows. Both the $PSFAdj and Count numbers will change occasionally throughout the data set so a static reference to the cell or number doesn't work.

Thanks in advance. I appreciate all your help and time. Have a great one.

I have attached a sample and have manually colored the cell according to how the formula needs to work.

Condition 1; If the information is coming from column 92 fill color needs to be blank if greater than today, if less than today s/b red. (92 is a forecast date)
Condition 2: If the information is coming from column 93 fill color needs to be green. (93 is a actualized or completed date)

=IF(COUNTIF(Sheet2!$E$4:$FV$100,$A19)=0,"",IF(VLOOKUP($A19, Sheet2!$E$4:$FV$100,93,FALSE)="",VLOOKUP($A19, Sheet2!$E$4:$FV$100,92,FALSE),VLOOKUP($A19,Sheet2!$E$4:$FV$100,93,FALSE)))

Thanks in advance for any and all suggestion.



i need help with a excel problem i am having, not sure if this requires more work outside of what excel can handle. I am also using excel 2010

i have 2 worksheets under the one file

One worksheet counts how many times a staff member is marked under a certain product code, (See screenshot 2 for sample data). Lets call this worksheet as TALLY
while the 2nd worksheet has the dates of the year and staff members. Each day is represented as the staff member working under a certain product code. So the first sheet will tally up the codes separately and give me a read out for the year. (See screenshot for sample data). Lets call this worksheet as YEAR TO DATE

Screenshot 2.JPG

My first worksheet has the following formula that works correctly for each product code and corresponding staff member
=COUNTIF('YEAR TO DATE'!$5:$5,Lookup!B$2)
but the data is calculated as i have specifically told the formula to look at row 5 to count the data for Staff 1. and i would do the same for the rest of the staff but telling it to look at a different row.

but as staff members arent always in the same row for the YEAR TO DATE for the entire year, as we have new starters added, i would like to use some sort of lookup that mentions

whatever is in the TALLY worksheet column A for each staff member, find the relevant row in YEAR TO DATE and countif the requried formula as above, rather than me having to specifically say in the formula go to row 1 etc

is this possible in the realms of excel formula's? or would i need to create custom actions?

if it helps me by uploading a sample version of what i need then i will do so


I have a countif page whose data changes weekly. Each week I have to go in and manually resort the list largest to smallest. That's my first question: is there a way to have the list automatically sort?

But the more annoying problem is that when I sort largest to smallest, the cells maintain their original reference point. So while, in a list of Apples, Oranges, and Pears, upon sorting, Oranges might wind up on top, the count of oranges is incorrect, because the cell references get switched up. So let's say that Oranges has the highest count and is currently in A3, and the Countif forumla in B3 says =COUNTIF('Data Source'!A:A,Calculations!A3). When I sort largest to smallest, Oranges winds up in A1, but the Countif cell reference stays at A3. A crucial point here: this will not occur if the countif is counting from the same sheet. But if it is counting from another sheet, the problem occurs.

I really don't know if I'm explaining this well, so I've attached a sample sheet.

In the sample attached, on the "Calculations" tab, sort fruits by count, largest to smallest. You may have to manually calculate after sorting, not sure if that setting transfers.

I have come up with a workaround. When I sort, in the case of the example, the Fruit column will be accurate, it is just the Count that is wrong. So I redo the formulas again and all is well. But it is an extra, annoying step, and one I have to remember to do.

Thanks for your help.


I have attached a sample spreadsheet which includes a list of individuals with different fields. As an example, I want to do a count of all the males who have a "New or Existing" status of "E" and who have a Join Date of "Mar-2011".

However, I want the count to change based on the date that is enetered in another cell. So if I put "Apr-2011" in one cell, the count will change by matching the table's "Join Date" with the date I put in the cell.

This is what I have so far, but the formula is not being accepted.

COUNTIFS(SampleData[***],"M",SampleData[New or Existing (N/E)],"N",TEXT(SampleData[Join Date],"MMM-YYYY"),TEXT(Q4,"MMM-YYYY"))

Any help would be greatly appreciated.



Hi, i'm trying to prepare statistic report by month. But i'm still noobie as i can't created Multiple Range And Criteria.

i'll give you the sample :-

1. this is my Data, should be look like this.

2. Table i like to created should be look like this.

i've been trying to used this code "=COUNTIF('CLPU USED'!H8:N3659,A1)" but this code only cater for 1 criteria. etc. with this code i can only get criteria for "MB/MC". but my report need to cater figure for "MB for month of JULY/any month" or figure for "MC for month of JULY/any month"

can you guyz guide me, please

Please refer to post #4 for more Nfo.

This is a sample countif function, and I'd like to count how many "VL" after datehired/aniversary date to end of the calendar year .
I'd just like a single formula in NG3 that returns a total number of 2 "VL"s

please advice.... thank you!!!

Is it possible to combine the Frequency and Countif formulas?

Here's what I'm trying to do: I have a worksheet that has a list of employee IDs and a list of months. I would like to count the number of unique ID's that occur within a certain month. My goal is to calculate the percent of ID occurances by month vs. the total monthly occurances. Each occurance equals and error, so I'm trying to find the average error percentage to set a baseline.

I've been able to get total unique IDs using the frequency, but not add a condition to count only unique ID's within a specified month. I've attached a sample with the data I'm using. Frequency Sample.xlsxAny help would be greatly appreciated!


I'm using Excel to schedule employees shifts at my restaurant. Column A is their name, Column B is their start time, Column C is their end time.

I'd like to be able to count the number of employees working during any given hour of the day to assure I've got enough staffing each hour.

I'm thinking it will require a COUNTIF function but I can't figure out how to make it work. I've attached a sample sheet if that helps. Any assistance would be greatly appreciated.

I'm trying to put together a workbook for auditing where there a series of questions in a column, and the user enters a y/n in the cell along the row to say where the conditions in the question have been met.

The questiosn are broken up into sections - so it's not a continuous row of y/n's - there's a total cell every few cells (not an even number) down with a countif formula that spits out a percentage according to how many yeses there are.

At the bottom theres an overall total that does the same thing.

The y/n column represents one instance of a certain task that's been audited, and there's a sheet per day with a whole heap of samples (has to be a certain percentage fo the total tasks done per day)- so there can be any number of y/n columns after the questions.

The problem I'm having is that if the user enters a space after the y/n, it's not counted by countif.

I could use data validation to restrict cell entries to JUST n/y, but I'm reluctant to do it that way as it will be a pain to add the validation to all the cells it would need to apply to, it won't be quite so user friendly and it looks pretty ugly - are there any other ways I can do what I need to do?

Dear excelers,
Got a question that need help:

I am trying to count from a list of numbers which I can change the sample size related to the 'countif' function.

Example: I input "10" or any range to count from 10 samples or so (see image below). Them the countif function only counts from these numbers! can this be done! please help....


I just finished creating a complex routine that creates a very readable and editable excel spreadsheet for end users from a database. The sheet shows student names in the far left column followed by columns for each school day of the month up to 23 days. In each cell it shows the lunch item ordered from 3 choices identified as options A,B and C. The countIf function used to work great until we started making it an option to order multiple items and multiple quantities of each item. I worked hard with them to come up with a very readable and editible format for the end user but now I don't know how to get daily column totals for each menu option.

As an example, I want to total the following column of text values that may have any of the following sample values:


I want total cells at the bottom of each column that would total the value of each letter option like the totals shown below of the sample column above:
Total A = 12
Total B = 7
Total C = 2

I tried using the formula Countif(Range,"A") which works fine if the cell contains "A" but it only checks the cells entire contents and not occurances of the criteria within so the values 2A, A+B and 2A+B do not increase the A or B totals. I tried using the search function but it doesn't seem to work from within the Countif() function or I don't know how to do it. There are thousands of these cells and I can't afford to create new cells to parse the contents of each and then total those.

Thank you for any help you can provide.

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