Countif Evenly Divisible By 5

Sep 22, 2009

I have a column of numbers in cell A1 through A10.

In cell A11 I would like to count how many times the numbers listed from A1 through A10 are evenly divisible by 5.

How would I do this?

Random Numbers Not Divisible By 3

Feb 16, 2009

With a formula, how could I generate random numbers between 1 and 100 that are not divisible by 3?

I made this part of a formula that generates the numbers not divisible by 3:

MOD(ROW(INDIRECT("1:100")),3)0

But I am unsure how to use it to help to generate random numbers between 1 and 100 not divisible by 3.

Determine If Range Is Divisible By A Number?

Aug 24, 2012

I have a report of time balances that employees enter. I need to run a macro that looks at each cell and determines if the number is divisible by 4. (We only track vacation and ill time in 4 hour increments) If the number the employee entered is not evenly divisible by 4, I want the cell highlighted.

All of this I have figured out, the loop to look at each row, the IsNumeric function to test if there is a number...

My problem is with the MOD. I figure I should be testing number MOD number, but it is not reliable.

4.1 Mod 4 returns 0
.5 Mod 4 returns 0

I just need it to return zero if the number is evenly divisible by 4.

I considered the RoundUp function, but then it would also round anything above 3 hrs to 4, and that won't work.

Check If A Date Is Divisible By A Number

Aug 28, 2012

I am trying to work out if today is in a sequence of +30 days from a specific date (and not past a specific date)

so, for example i have a start date of 25/1/11 and an end date of 25/1/16.

i want to know if today falls as part of a 30 day cycal from the start date.

i need to be able to do it without working it maunally such as just "=25/1/11+30" and fill across.

Count Number Of Divisible Numbers?

Apr 26, 2014

I am trying to work out how to count the number of cuts that might be made in a length of pipe, dependent on the length of the source material and the cut lengths required. This is so I know how much to charge for the activity.

So for example, the source material is 3600mm in length, I need 3 lengths of say 1100mm which gives me waste of 300mm (that doesn't matter) but I need to be able to calculate the number of cuts made (in this case 3) for any variation of the cut lengths. The 3600 only varies from time to time but the cut lengths change all the time.

Conditional Formatting :: Color A Cell If It's Divisible By 10

Oct 30, 2009

Is it possible to use conditional formatting to to turn a cell red whenever the value in the cell is divisible by 10 ?

Custom Filter To Select A Value And Values Divisible By That Number.

Feb 5, 2009

As I have not tried to do this yet this is a hypothetical scenario. Imagine that a column, when filtered, gives cell values such as 3,6,9,12,18,24,36 ..and so on. Is there a way to select, say 12, and then see all the other values that are divisible into 12. e.g. all rows that have 3,6,12. Another example- select 36 and see 3,6,9,12,18 and 36?

On the attached example sheet, column 'O' will be filtered.

Dividing Numbers Evenly

Apr 6, 2009

I have a total at the bottom of my spreadsheet and I want to distribute that total equaly to the missing columns for each month and each product highlighted in green in the spreadsheet. What formula do I need to use to be able to do that?

At the end I want the numbers in each month to total the total at the bottom.

Reduce GP Percentage Evenly For Two Departments

Aug 14, 2013

When we sell things here at a 'sale' price there is always an argument as to who's department needs to give the discount off.

Each job we do has a labour time and an hourly labour rate.

Each part has a profit in it.

So if something is normally £100 but we are going so sell it for £80 we need tio split the £20 reduction evenly to both departments based on the GP each department has in the sale.

See attached : GP Reduction per department.xlsx‎

Sep 10, 2013

I'm working on a project that will will have varying tasks & estimating the total duration to complete these tasks.

For example:

If estimating that the min-project will take 90 days & I have 17 tasks, how can I distribute the 90 days evenly amongst the 17 tasks?

Formula To See If Cell Divides Evenly Into 5

May 22, 2014

I need a formula to do two things I need it to look at Cell C2 and if it does not contain a number then I want the letter A put in the cell, if it contains a number then if that number is divisible by five giving a whole number i.e like 10,15 20 would, then input 1, but if not 2.

How To Spread Existing Data Evenly Throughout The Same Row

Sep 28, 2009

I don't know how else to ask this so I will just tell you what I'm trying to do.

-I have a list of name on one column say "A"
and this list of names goes through "A1" through "A145"

-I am taking those names and pasting them via "right click" "paste special"

-BUT I have to take each name one by one and paste them evenly
in every 8th row for example ( "A1" A8" "A16" "A24" ...)

-Is there a way to take the existing data on that row and just spread it evenly throughout the same row? So I don't have to take names one by one by one...

Evenly Distribute Hours Over Month

Dec 9, 2006

I am currently tracking our company's service tasks in Excel and would like a formula or method that will take the total hours a task has been approved for and equally distribute the hours into columns under each month.

Column A = Resource
Column E = Approved Hours
Column F = Start Date
Column G = End Date
Column H = Jan 07
Column I = Feb 07
Column J = March 07 etc. etc.

I basically want to take the approved hours (Column E) and have those hours automatically calculated and filled into the month columns (Column H forward), as per the start and end date fields (Column F & G) so the hours are evenly distributed over each month.

Increase / Decrease Numbers Evenly For Spreadsheet

Aug 27, 2012

How to increase & decrease numbers evenly for a spreadsheet.

I have a number in cell F16 that can be changed and for this example say it has a value of 900.
I have a number in cell F17 that is always more than cell F16, for this example lets say it is 1000.

What I'm trying to do is create 10 numbers evenly increasing away from 900 (F16) in value, & 10 numbers evenly decreasing in value away from 900 (Cell F16) but here's the catch.

What makes this tricky is that the numbers that are increasing in value away from 900 (F16) can't be bigger than the value of F17 , IE in this example the number 1000

So the formulas must evenly distribute 10 numbers BETWEEN 900 & 1000. This must have the same even distribution for the increasing numbers & decreasing numbers.

I'll attach a sheet also. So, if you say 900 is a starting point & 1000 is the ending point, what formulas would you need in each cell to evenly increase to 1000?

Also,, going the other way, decreasing, away from 900 at the same rate as the increase?

Really stuck n this 1.
Screenshot here: [URL] ......

Auto Stretch Text Evenly Across Merged Cells?

Dec 28, 2013

I am using a staff schedule spreadsheet and have each column displaying text of where a staff person is assigned.

Instead of displaying the word "lunch" separately across several columns, is it possible to merge several columns and have the word "lunch" automatically stretched evenly across the several merged columns?

I dont want it to be centered but to literally be stretched to fit evenly across all of the merged cells.

Distribute A List Of Data Evenly Throughout The List Of People

Dec 9, 2008

I am trying to distribute a list of data evenly throughout the list of people I have.

I am not sure how to describe it so I will upload the example excel as an attachment.

COUNTIF (b4:b65000= &quot;Name&quot; Then Countif G4:g6500=&quot;BI&quot;)

Apr 21, 2009

I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.

For instance:
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.

Sam Douglas:BI
Sam Douglas:BI
Sam Douglas:SI
Sam Douglas:BI

BI = 3
SI = 1

How To Countif

Jul 14, 2009

I have following data (two columns Parent and Child), now I want to apply Countif on Child cell.
But in Countif I want to provide the criteria...let say only count those childs whoes parent is A.

How to do this in Excel.

Parent Child
A e
A f
B g
B h
B i
C j
C k

Countif: Others

Aug 11, 2009

I'm reasonably new to Excel, and have a fairly basic question to check out:

I have been using the COUNTIF function to count up numbers of items in various categories in a column.

The formulae I have been using are like this:
=COUNTIF(F\$3:F\$201, "Red")

or where I've wanted to combine various comments
=SUM(COUNTIF(F\$3:F\$201,"Yellow")+COUNTIF(F\$3:F\$201,"Cream"))

I'm not sure what formulae to use to count up
1) the total number of entries in that column, so that I can make sure that I haven't missed some (without having to check manually!)

2) how to count up the values that do not match the other categories that I have specified in the COUNTIFs: this would be a value for finding how many 'other' entries there are in that column, without having to specify those values

COUNTIF With Two Variables

Jul 6, 2012

I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.

What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4

I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")

Function If And Countif Together?

May 14, 2014

important columns are: "D", "G", "L".

this is about a project is a granite to be installed in 104 units, these units are divided in 4 types (column "L") K1, K2, K3, K4

I tried IF, SUMIF, COUNTIF, ETC but I think that I need is some combined function that I'm not able to figure out.

as you can see in the attached file, my units 101, 102, etc is not been installed yet (column H is empty), but the units 209, 210, 211, 214 yes was installed on 5/6 for example.

for example, if i use =COUNTIF(H4:H20,">0"), will return me all the H that is not empty (=3), how many kitchen was installed so far, but, I would like to know how many of these returned units (3), is my different types: K1, K2, K3 and K4....

COUNTIF With 2 Variables?

Aug 1, 2014

I am trying to count the status and type of some work so:

Column A would contain the status of the work e.g. open, in progress, closed etc.
Column B would contain the department: ict, development, operations, etc.

I want to do a summary that shows: How many are in ICT are open, closed etc.

I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.

CountIf Across Two Cells

Dec 14, 2013

What i want to do is count but only if another cell meets a certain criteria,,

Let me explain better,,what i want to do is count the number of 'Completed' cases by each individual, i.e. count if the cell A1 = Completed and B1 = Kevin and the same for 'Pete'

I'm sure i need some sort of nested =IF AND formula but just can't think how the nesting should look.

B
CompletedKevin
CompletedPete
CompletedPete
Parked Kevin
SubmittedKevin
CompletedKevin
Started John
Started John
Started John

And Sum Countif Statement

Dec 17, 2013

I have attached a copy of my spreadsheet .My formula is in column BA and I am trying to figure out which students have 5 A* - C. We are only allowed to add 2 vocational subjects to this formula so basically 3 (or More) GCSEs + 2 BTEC/OCR Nationals. Now my formula works pretty well except if the candidate has 4 GCSEs and 0 or 1 BTEC it is still calculating as though the candidate has achieved 5 A* - C.

I have highlighted GCSE Subj in pink and Vocational Subj in Puple.

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.

CountIf Within A Range...

Feb 3, 2009

- Column D includes dates.
- Column E includes text, either "Yes" or "No".

I want Excel to count all cells in Column D between 1-Jan-2009 to 31-Jan-2009 + any cells that say "Yes" from Column E on the same row.

Countif With Two Variables

Apr 14, 2009

I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".

Sum And Countif From Two Cells

Apr 21, 2009

I have a formula =IF(AND(F14=3,G14<2)=TRUE,1,"") which is in cell J14. I also want to 'add' to this formula an 'or statement' (I am not sure of the terminology, so taking a shot in the dark). So it would continue to complete the calculation as normal, but if there is a '1' in K14 it would also output in J14 as a 1

Sumproduct Vs Countif

Jul 2, 2009

I have a worksheet where I am trying to count the number of occurences of several text strings.

For example:

I'm trying to count how many times "paid in full" and "fully paid" occur in column A.

I have two formulas, and both seem to work, but since I don't really understand either of them, I'm wondering which I should use and how I would adapt it to include additional text strings. (Like adding "paid" to the list)

Here are my formuals (I didn't write either of them, another co-worker did)

=(COUNTIF(A:A,"paid in full"))+(COUNTIF(A:A,"fully paid"))

=SUMPRODUCT(--(A1:A50={"paid in full","fully paid"}))

Also, if there is another and easier way to do what I'm trying to do, I'd love to know.

Countif For More Than One Condition

Sep 18, 2009

I have a table with 2 columns - Gender & Age. Now, suppose I want to know the number of Males in the table with age greater than 14, how do I do that? Can a Countif formula be used for more than one condition or is there some other formula?