Using Count If But Skip Columns
Jan 20, 2010
I use the formula below to sum hours in a spreadsheet:
=((((SUM(B6:AP6)+(COUNTIF(B6:AP6,"x")*12.5))+(COUNTIF(B6:AP6,"d")*12.5))+(COUNTIF(B6:AP6,"m")*12.5)) +(COUNTIF(B6:AP6,"e")*6))
I would like to be able to change the range from "B6:AP6" to something which will start with B6 but only count every 6th cell up to AP 6. I could probably place a symbol at the top of each column I wish to count if needed. If I try to simply choose every sixth cell I get an error about too many arguments for the countif function The above formula is used to add hours for various shifts (symbolized by letters). For my current need I do not need the multiplier i just need to count how many of each letter appear in every sixth cell of a row.
This may be too fancy but if one formula would work to count every 6th cell starting with B6 and give me a sum , then automatically do the same starting with B7 and count every 6th cell etc...that would be great. I am trying to sum the number of each occurrences of each shift at each of 6 locations.
View 4 Replies
ADVERTISEMENT
Nov 15, 2009
How do you make the Autofill function increment in blocks, e.g. instead of increment 1,2,3 increment 1,8,15 inthe following example: I have a 2 worksheets, one with days of the year across columns and values down rows, and another that summarises the sum of the corresponding days in weeks, so I have 365 columns in sheet 1 and 52 columns in sheet2. Everytime I update the sum range i cannot autofill it in the summary worksheet, as A1:G1 autofills to B1:H1, but I want it to increment to H1:N1.
View 3 Replies
View Related
Jan 16, 2007
I want to figure a class average for a test, but I have students that have moved. My Excel is automatically counting them as a zero. How can I program/tell Excel to skip any blanks rather than count them in the average?
View 9 Replies
View Related
Jul 15, 2014
I have few column values to count. I don't want the hidden column value. In that formula the hidden value also counted. How to ignore that hidden column value count, using macro/formula.
eg:
col A - col B - col C - col D - col E - col F
Task -- ok1 -- ok2 -- no1 --- ok3 -- ok4
Col D should be hidden. Final count result should be: OK = 4; No=0
View 3 Replies
View Related
Oct 27, 2009
This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?
View 2 Replies
View Related
Feb 28, 2008
I am trying to compile a count of dates using a numerical reference which adds x consecutive dates onto the initial start date based on the number in a cell.The dates added cannot fall on a weekend.
The start date for each calculation begins in D4 with the corresponding number of days to add onto this date in K4. In the example below 8/11/2007 -- 2 means that the 8th and 9th of Nov get one added to their count. As the next row also has 9th Nov as a start date one more is added to 9th Nov. As the 10th and 11th were a weekend they are skipped and the count starts again from the next Monday. I have enclosed a simplified worksheet with some sample data.
Example:
D4 --------- K4
8/11/2007 -- 2
9/11/2007 -- 3
Expected output:
AE4---------AF4
8/11/2007 -- 1
9/11/2007 -- 2
12/11/2007 --1
13/11/2007 --1
View 5 Replies
View Related
Jun 11, 2013
I'm looking for a way to count the cells in a specified range if the cell contains a timevalue (such as: 0:05 or 1:15). When a cell in the same range contains a normal value (such as: 1 or 20) it should not be counted.
What formula should i use for this?
I was experimenting with:
=CountIF(N4:N50,">TIMEVALUE(0:01)")
and
=CountIF(N4:N50,">TIME(0,0,1)")
But both don't seem to work.
View 3 Replies
View Related
Dec 3, 2013
I have 2 sheets in my excel document. The first sheet (sheet1) has student data (grade they got on a test) from C5:C35 all in 1 column. On the 'second' sheet (sheet3), I have made a profile for each student (there are 30 of them, spread out 1 per printable page). I want to take Sheet1!C5 result and put it in sheet3!D7. The formula in sheet3!D7 would just be =sheet1!C5. I want to 'drag' that formula to L7, then to T7 then to AB7 etc etc (they are all 8 columns apart), obviously not affecting any of the cells between them.
There are 12 pieces of data in sheet1 that I want to auto update in sheet3, for 30 students, so copy pasting, and adjusting the formula would take a long time.
View 7 Replies
View Related
Feb 3, 2014
I am working on a =transpose function. Pretty simple right? Well now I am trying to have it SKIP/SELECT certain columns.
I have =transpose("date!B1:T1"), but now I want to "Select A,B,D,E,F"
=TRANSPOSE(INDIRECT("date!B1:T1"),"select A,B,D,E,F,G,H,P,Q,R,S,T")
View 1 Replies
View Related
May 1, 2014
I have below a code that is selecting from A11 down as far as there is data and across a certain number of columns then sets a print area.
Currently the columns go A, B, JW, JX, JY, JZ, KA, KB as C through to JV are hidden. I have tried to offset the columns by 7 to see if it will select A-KB but it is selecting cells that are hidden and only selecting A-G. I need it to go past the hidden columns. Here is the code:
[Code] ......
Could I also get a code to go from A10 and move over to KB and create a range from there e.g. KB-KJ and last row from there?
View 10 Replies
View Related
Feb 9, 2012
A post about skipping rows is very similar to my situation, but I'm stuck on how to translate that for columns. (Filling formulas but need to skip cells)
I'd like to transfer information from a worksheet called "Raw Data" to another sheet, skipping every 5th column. I'd like to go from "Raw Data" B12 to a new sheet C6, then from "Raw Data" G12 to D6 in the new sheet, and so forth.
I figured out that I'll need to use COLUMN()*5-13 as a component of the function, but I can't seem to figure out how to format the name of the worksheet correctly, with the ampersand and quotation marks.
This doesn't work at all, but I think it's in the ballpark...
=INDIRECT("Raw Data!"(COLUMN()*5)-13)&12)
View 3 Replies
View Related
Feb 22, 2007
I have been using the wrong formula to count total entries in columns and only just found this error. The MAX formula in cell B4 is: =MAX($B$12:$B$36). If the all the rows are full within range F12:F36, then the MAX formula is fine to count the total within range B12:B36 (25) so I thought. But sometimes there are omissions between F12:F36. If there are 2 blank cells anywhere within F12:F36 for example, then B4 needs to show 23 respectively. In the sample WkBk B4 needs to show 8
View 2 Replies
View Related
May 17, 2006
I have this in my Macro:............
My Macro runs fine when there is something in Cell S2 but if there is not then I get an error on "Cells(sumrownum, 19).Value = "Total" 'Column S". What I want to do is put a if then type of statement that if S2 is empty the it will skip down to "Columns("W:X").Select" and keep going with the rest of my macro.
View 6 Replies
View Related
Apr 1, 2009
Im trying to return the number value of how many contracts have been signed in a particular month. So in my example I want to look pick out say "IM" and check to see how many of those types have been signed in say "Jan-09" and return that as a number. Have tried various mixes of count, sum, countif and sumif but dont seem to be getting anywhere.
View 4 Replies
View Related
Jul 2, 2009
I have a column with different cities that customers visit (Barcelona, Lisbon etc) and another column with their booking requirements (All inclusive, breakfast only etc)
I'd like to set up a count on the total number of customers who are going to Barcelona and having all inclusive.
I've tried using nested if statements with vlookups and a countif but can't seem to get that working.
View 7 Replies
View Related
Aug 4, 2009
I am working on a spreadsheet and could use some help
I got a sheet where I need to pull the numbers listed under the columns
for example I have cut and pasted my data below. It did not paste well however I think you might be able to still figure it out.
I need to know how many Game 1's are being played in each week
and I need to know how many Game 2's are being played in each week.
TeamsGame 1Week #Game 2Week #Game 3Week #
Florida GatorsTroy2Tennessee3At Kentucky4
Texas LonghornsAt Wyoming2UTEP4Colorado6
USC TrojansSan Jose State1At Washington3Washington State4
Oklahoma SoonersTulsa3Baylor6Kansas State9
LSU TigersAt Washington1Vanderbilt2UL Lafayette3
View 12 Replies
View Related
Oct 13, 2009
I'm looking for a formula to allow two different columns to count for a result.
The formula i use now is this. =COUNTIF(I3:I34,"W")+COUNTIF(I3:I34,"Ws")
It counts "W" if it's a win, "Ws" for wins in overtime, "L" for a loss and so forth and so on. I'm now trying to use, in a different column, H and A for home and away games. And i would like to have it to where it can keep a home and away record next to the main record. And if this works i'll do a district record as well.
View 2 Replies
View Related
Jan 20, 2010
I have columns from A to AA and need to count the duplicates in at least 2 columns and accordingly need to give them values on the basis of their ratings (A, B, C, D) and type of work (book, article etc) given in yet other columns.
I tried COUNTIF, but that does not work for multiple perimeters
I tried SUMPRODUCT, =SUMPRODUCT(($G$4:$G$3000=$G28)*($AA$4:$AA$3000=AA28)), but that only gives a multiplication of my input without the possibility of giving new values to the output.
I tried AND(IF( but that did not want to work either really...
I think the solution is ifsum, but I do not know how to use it in this scenario...
I know this all sounds a bit blurry, so let me attempt to make it more concrete.
Author Title Year Rating Type Desired outcome
P Marx I 2005 I A I Book I duplicate
A Hegel I 1923 I B I Article I -
B Kant I 1674 I D I Book I -
A Derrida I 2005 I D I Monograph............................
View 5 Replies
View Related
Sep 7, 2009
I need to find a formula on cell A2 on the attached file. The formula should show me the count of Customers who buy SKU 1 only.
I tried to use filters across SKU (2-16) where there is "0" and then count all customers who have values >0 on Column B. But this takes a long time and I have to do similar tables quite a few times. I was just hoping if anyone can come up with a more efficient solution to it.
View 6 Replies
View Related
Oct 20, 2011
We have a sales table that 23 sales people update then send back to me. So each week I receive 23 worksheets.
The number of columns in the tables I get back can vary, depending on what each salesperson is reporting for that week.
I have written a macro that takes each sheet, automatically finds the last column and then adds two new columns of summary data.
I use COUNTA on the header row to find the number of colums. This usually works fine - but there is one problem that can occur.
Sometimes people insert one or more blank columns in the table. So how can I find the last column, because COUNTA won't work? (I do not want to simply delete these columns because some people have set up vlookups and their own macros based on the position of the columns they have defined, including the blanks.)
View 2 Replies
View Related
May 11, 2012
In a single cell I want to return the number of columns in a matrix which contain entries. So, for the matrices below the formula should return '2' (two columns contain values):
A1: 0 , B1: 0, C1: 1
A2: 1 , B2: 0, C2: 1
A3: 0 , B3: 0, C3: 1
A1: d, B1: e, C1: 0
A2: 0, B2: f, C2: 0
A3: 0, B3: g, C3: 0
View 3 Replies
View Related
Nov 12, 2012
I want to be able to return the column number depending on the cell.
For example I have the months of the year horizontally from f6:Q6 and I need to be able to return what column number a certain month i depending on the month I type in cell A1.
View 4 Replies
View Related
Nov 27, 2012
Is there way to do this? I did not find it.
View 5 Replies
View Related
Dec 2, 2008
I am trying to count when two cells have been filled in different columns. As shown below I need to count how many starts are JCP and how many SELF. I am thinking in plain english if B2 is filled and C2 is filled then count and show total in A10.
Self JCP Start
01/02/2008 01/03/2008 02/02/2008
02/03/2008
03/02/2008 03/03/2008 04/02/2008
04/03/2008
05/02/2008 05/03/2008 06/02/2008
06/03/2008
View 9 Replies
View Related
Jan 25, 2008
Countif must meet criteria in 2 different columns. I can get one criteria in, but not both.
View 3 Replies
View Related
Apr 23, 2013
I need to count how many times a code appears over a 6mth period only on a single day. The data would look something like:-
1/1 2/1 3/1 4/1 5/1 6/1 7/1 8/1 9/1 10/1 etc
a b c a a b a a b b
If counting b the result would be 2 on the above 10 days as it appears on the 9/1 & 10/1 it would not be included. It is to be used to count the number of times somebody only appears on a single day over a period of time but not count them if they come back the next day.
I cant think of a way to do this using in built formulas (Countifs, sumproduct, etc) The example is basic and there could be up to 20 codes.
View 4 Replies
View Related
Apr 29, 2014
I have a list that tracking teams, specifically, the game number of the season. I also would like to start the count over every month. I have 3 columns to consider in the formula - 1) the month, 2) the home team, and 3) the visiting team. What I hope to accomplish is something like I have listed below. The # columns are blank in the real file - this is just a small example to illustrate what I'm after. Is something like this even possible? Attached is an excel file with this same data.
example.xlsx
View 2 Replies
View Related
Feb 25, 2014
I have this work sheet with several formulas in columns Z to AD. All of them highlighted red work fine as for as I can tell. I am stumped with the one needed for the cell highlighted yellow AD2. It should count all the dates in AD1 that are Requested Changes Made and/or Rejected in Column "M". AD2 is a total of today minus 8. Equipment Change out - TEST.xlsx
View 5 Replies
View Related
Mar 13, 2014
What I'm trying to do count the TRUE values in multiple columns, if the criteria is correct in another column.
I've tried countifs but end up having the company included into the count, or only count the row that matches all the criteria.
If I do =COUNTIFS(A2:A7,"A",B2:B7,"TRUE")+COUNTIF(C2:C7,"TRUE") then I get 5.
When I change it to +COUNTIFS(A2:A7,"A",C2:C7,"TRUE") it works but there's a time where I need to check up to 8 Options.
Company
Option 1
Option 2
a
True
True
b
True
False
c
True
False
a
True
False
a
True
False
b
True
True
View 10 Replies
View Related
Mar 25, 2014
I have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left. I believe you need to use the subtotal function, but I do not understand how to use it.
View 14 Replies
View Related