Excel 2003 :: Formula For Counting Values Across A Range Using Multiple Criteria Across Multiple Sheets
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
ADVERTISEMENT
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
Jan 1, 2014
How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs
View 3 Replies
View Related
Dec 20, 2012
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
View 1 Replies
View Related
Jun 12, 2014
I have a couple of issues and if its okay will post two threads to make it easier to follow due to my basic skills in Excel.
I have a workbook with 5 sheets. The first sheet is a stat sheet which picks up data from four other spreadsheets. I want to work out the average working days taken to complete a case but this data is across multiple sheets.
So for example Column E in each sheet tells you what type of case it is i.e. investigation. Then column T tells you number of workings day it took to complete the case. To work this out I have used the following formula:
=SUM(SUMIF(Further_Action!E:E,"Investigation",Further_Action!T:T),
SUMIF(Court_Application!E:E,"Investigation",Court_Application!T:T),
SUMIF(No_Further_Action!E:E,"Investigation",No_Further_Action!T:T),
SUMIF(Closed!E:E,"Investigation",Closed!T:T))/SUM(COUNTIF(Further_Action!E:E,"Investigation"),
COUNTIF(Court_Application!E:E,"Investigation"),COUNTIF(No_Further_Action!E:E,"Investigation"),
COUNTIF(Closed!E:E,"Investigation"))
I know its very basic but its the only way I know how to work out an average in Excel 2003. I need to add a second criteria where on every sheet it looks in column G for date the case was allocated. As I'm only looking to report in the current report year I want it to look for cases where the value in column G is =>01/04/14 but =<31/03/14.
View 2 Replies
View Related
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
View Related
Oct 20, 2012
I have a table in A2:F200 and want to sum the entries in column C for those rows containing both an F in column E and an L in column F. I thought this would be easy but I cannot figure it out.
I use Excel 2003 so SUMIFS don't work.
View 2 Replies
View Related
Aug 13, 2014
I am looking to count the unique amount of customers who are listed in column C based on the criteria that they purchased the product on the 1/08/2014 and that the product came from Department 3. The output I am looking for in this example would be 3. I can do it for this example but when I have 300 different customers it starts getting tricky and I'm a bit stumped on how to incorporate an array formula into a countifs() function or whether there is an alternative.
Date DepartmentCustomer
1/08/2014 3 A
1/08/2014 3 B
1/08/2014 2 C
1/08/2014 3 D
4/08/2014 3 A
5/08/2014 2 A
5/08/2014 3 D
View 7 Replies
View Related
Feb 25, 2009
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B
2366 2005 T 2
(as opposed to 3)
A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372
View 9 Replies
View Related
Feb 26, 2009
I'm trying to get a count of unique values in column B when criteria matches for columns A,C and D. The worksheet I'm trying to complete looks like this:
2366 2005 T [number of corresponding unique values of B]
For example:
--D---C---A-B
2366 2005 T 2
(as opposed to 3)
A
B
C D
T
655 2005 2366 T
656 2005 2366 T
656 2005 2366 W
659 2005 2367 W
659 2008 2369 W
659 2006 2370 F
659 2005 2370 W
660 2005 2370 W
660 2008 2371 W
660 2006 2371 T
660 2005 2371 W
661 2005 2372 W
661 2007 2372
View 9 Replies
View Related
Jan 26, 2013
How many tickets are created and fixed by a user for a certain period. See below. I've tried countif but it doesn't seem to be working for many criteria.
Start Date
11/1/2012
End Date
11/31/2012
Assigned To
Created
Fixed
[Code] .....
View 8 Replies
View Related
Mar 20, 2014
So I am trying to pull data from multiple sheets. I've gone through the thread, but haven't found an answer yet (or didn't work hard enough). On Sheet1, yellow highlighted column, I am trying to look up the I.D. Code for 36 month residuals. As you can see, I have to use multiple conditions on different worksheets.
I have to use most of Sheet1 columns to find the answer. I just can't figure the formula out.
I don't have Excel 2007, only 2003 I have.
View 1 Replies
View Related
May 10, 2012
I wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
View 5 Replies
View Related
Mar 3, 2008
I have been searching the web and wracking my brain trying to do this and now I am here (for one place to start) for help. Especially since I got the formula below from this forum.
1. I have a worksheet with multiple sheets. All the sheets except the last are labeled by department. The last sheet is labeled Totals. All the department sheets follow a template that I don't want to change so any totals or formulas I want to add, I want to do off of the Totals sheet.
2. For an example, I want to total all PCs with the model "Compaq 1.8" from all the sheets onto the Totals sheet. It doesn't seem like Countif can do multiple (or 3d) sheets. It can only do one sheet.
It looks like the only way to do this is to use a UDF (User Defined Function) or some other Function to do this.
I found a UDF that looks like it should work (see below). However, I have tried this function but all I get is the #NAME? error/message. I have tried multiple ways to simplify this function just to test that I can get any data from it but keep coming up with the #NAME? error.
Public Function CntIf3D(rng As Range, V As Variant, ParamArray arglist() As Variant)
Application.Volatile
CntIf3D = 0
For Each arg In arglist
CntIf3D = WorksheetFunction.CountIf(Sheets(arg).Range(rng.Address), V) + CntIf3D
Rem Next
End Function
If I could get the above code to work, It would be nice but I would have to add another department into every total for each new piece of equipment. Some code that would just use the sheet indexes would be nicer, that way any new department inserted before the TOTALS sheet would be automatically added in. Since I can't even get the above code to work, however, I don't want to waste my time trying to test code that would use the indexes.
View 9 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
Jun 26, 2014
I am working on an attendance spreadsheet in Excel 2003 at work that will display data showing number of learners allocated to an activity, number unallocated, number attended, number not attended, number of acceptable reasons and number of unacceptable reasons by Block Letter. My problem is that for a number of reasons the same learner number is allocated to a number of activities each day. I need to know how many learners from each block have been allocated, not how many allocations each learner has from each Block.
I have tried different formulas, but fast running out of time
I have attached a modified sample from the main spreadsheet
I need : Cell H4 to calculate the number of unique values from Column C on Mon tab but only meeting the following criteria Mon!$N$3:$N$4000="AM", Mon!$O$3:$O$4000=D4, Mon!$B$3:$B$4000="mon1" I can then apply to the other rows.
View 7 Replies
View Related
Jun 17, 2014
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
View 3 Replies
View Related
May 29, 2013
I have the following table
Team>
Team A
Team A
[Code].....
I need to fill the following table in another sheet counting the amount of Blank cell there are according to Month, Team and if the name row is filled. I have tried Sumifs, sumproduct,countblank typing them in as arrays but don't seem to be getting anywhere
April
May
Team A
6
1
Team B
11
1
View 2 Replies
View Related
Feb 25, 2008
I have an annual leave (vacation) work book with a summary page work sheet and separate work sheets for each month i.e. Jan, Feb, Mar.....to......Dec. In order to protect the formulas I have protected them by allowing only access to the input cells on each work sheet and the protect each work sheet.
When someone either joins or leaves the team I have to manually unprotect each sheet and protect again when I have completed the amendments to each of the 13 tabs.
macro code I would need to unprotect all the works sheets in one go (as I use the same password for all the sheets) and reset the passwords (protect) the sheets with more macro code.
I will be running the two macros from my own personal.xls file and ideally they would be fully automatic i.e. I would not need to input the passwords in to unlock or lock the work sheets as the password would be written in the code already
Excel version 2003
View 3 Replies
View Related
Jun 24, 2012
I need to calculate the average spend on a day of the week over the month, so all Monday's or all Tuesday's, etc. One sheet is one week so I need to average b16 on 6 sheets as an example.
I used =AVERAGE('WEEK1:WEEK6'!B18) to calculate average over the six Monday's. The issue is, as in other posts, how do I ignore the cells that have a zero or null value.
I've tried adjusting this which was in 1 post
=AVERAGE(IF($C$2:$CA$2=C62,IF($C$25:$CA$25"",$C$25:$CA$25
with this
=AVERAGE(IF(1+1=2,IF('WEEK1:WEEK6 '!B180,'WEEK1:WEEK6 '!B18))) which returns #REF!
this from another post
=SUM('WEEK1:WEEK7 '!B18)/COUNTIF('WEEK1:WEEK7 '!B18,"0") which returns #VALUE!
and this
=AVERAGE(IF('WEEK1:WEEK7 '!B180,'WEEK1:WEEK7 '!B18)) which returns #NAME?
The cells on each sheet are sum formulas for other cells on the sheet not just numbers on their own.
Using windows 7, excel 2003
View 3 Replies
View Related
Mar 8, 2012
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A"
=COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year
=SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
View 7 Replies
View Related
Dec 2, 2011
I have a sheet that column G and column H has duplicate values. I would like to be able to put a formula in a cell P4 that will search thru column G and column H and if there are any matching items then add the corrisponding number in column I.
So say cell G8, G25, and G30 have the same and H8 and H30 have the same value, i would like this to add the values of cell I8 and I30.
Windows XP
Excel 2003
View 9 Replies
View Related
Apr 20, 2013
I am using the following formula:
=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
Using Excel 2003
View 9 Replies
View Related
Dec 20, 2012
Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"
View 5 Replies
View Related
Nov 13, 2013
I'm using Excel 2003 and I need to calculate the number of occurrences on according to different criteria in two separate colums.
I am on Sheet 3 and the data I am analyzing is on Sheet 1, titled "RATES". I wish to know how many occurrences are when the word "GB" appears on column B (cells B2 to B100) along with a value greater than zero on column M (cells M2 to M100). The word "GB" does not appear alone but is part of a string of text with different words, I think this is relevant.
View 2 Replies
View Related
Mar 21, 2014
I am trying to implement a count on a table that splits up the number of customers associated with a list of managers. Sample data attached.
Count function.xlsx
So, I need some way to display a single instance of each manager name with a count of how many customers are associated with that manager. The number of managers and the number of customers associated with each manager does vary and are displayed in separate columns per the attached.
View 14 Replies
View Related
Feb 9, 2014
I have attached an example of a Trail Balance workbook that shows monthly tabs and a summary tab. Column A&B is just a small sample of the Account #'s and Account description for each month. Obviously i can just Sum the different sheets on the summary page. However, the issue i have is that on the complete detailed sheet (over 200 rows/accounts down) that i have to complete columns A&B don't match up exactly and have added accounts in them or removed. So i need a way to consolidate all sheets in to a summary page making sure all accounts are listed on the summary page along with values summed into the various columns.
View 3 Replies
View Related
Dec 21, 2011
I have these postcodes as example below but the array formula I was going to use won't work because, for example when I count everything with the Birmingham post code 'B' it counts every thing that contains the letter B which could also be in the post code BA1 3RL?
Excel 2003FGHIJKL2AB11 7TFWEB3ECRAB143AB12 3NFWEB3ECRAL54AB14 0QNWEB396FECRB1295AB15 4ANWEB34ECRBA86AB15 5LRWEB34ECRBB4Sheet1 (2)
View 5 Replies
View Related
Jun 24, 2014
I've attached a sample sheet to this message.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Sample Data.xlsx
View 4 Replies
View Related
May 5, 2013
I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.
I have been using the SUMIF formula as follows:
=SUMIF('Sheet2'!A8:A10,'Summary'!A4,'Sheet2'!F8:F10)+SUMIF('Sheet3'!A8:A15,'Summary'!A4,'Sheet3'!F8:F15)
And so on for each sheet and then change the formula for the next skill set and then again for the next column.
Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.
So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.
So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.
View 5 Replies
View Related
