Counting And Percentages In Multiple Sheets

Jan 22, 2008

i have 4 worksheets with various names. i have an 5th worksheet with a named list (TabNames) of the 4 worksheet names

each of the 4 worksheets have cells with percentages in them.

i would like to check a cell from each sheet and see if the percentage is equal to or above a certain %, for example anything equal to or above 90%. then return the number of cells that meets or exceeds that 90% criteria.

so for example

sheet1 sheet2 sheet3 sheet4
A
1 80% 91% 76% 82%
2 80% 95% 92% 43%

so checking cell A1 in list TabNames would return 1, and checking cellA2 would return 2.

View 14 Replies


ADVERTISEMENT

Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

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

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 View Related

Counting Non-zeros Across Multiple Sheets?

Mar 11, 2013

I am trying to calculate nonzeros across multiple sheets, rows, columns, etc.

I understand the countif(range,>0) however it will not calculate my range when I try and separate the cells with commas.

What is the best way to count?

View 4 Replies View Related

2000 Counting Same Range Across Multiple Sheets

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

Scale Of Percentages Using IF Statements And Percentages?

Jun 27, 2014

I need a formula to create the following:

In Cell F5 is 26, so the percentage should be 101% (100+1). How can I modify the below to add 1% when the number goes over 100%?

Do I have to create two columns? one for the below and one if the F6 is over 100%?

IF(F6="N/A","N/A",IF(F6>=25,1,IF(F6>=19,0.8,IF(F6>=13,0.6,IF(F6>=7,0.4,IF(F6>=1,0.2,IF(F6

View 5 Replies View Related

Splitting Out Value In Row Into Multiple Rows Based On Particular Percentages?

Apr 11, 2014

I have two tables of data (each on its own sheet). One contains cost centers and dollar values (Sheet1), the other contains cost centers, secondary cost centers, and percentages (Sheet2). Each cost center can have any number of secondary cost centers, from 1 to almost 100.

The issue is that I need to have the single-row cost centers on Sheet1 broken down on a third sheet where the dollar amounts are distributed to the secondary cost centers according to the percentages on Sheet2. There is a lot of other data that will be pulled into this but that doesn't need to be modified in any fashion.

I can do it in Access, but doing it purely in Excel (which is the task I am given) seems problematic.

I tried using the Microsoft Query, but it only allows me to query from earlier versions of Excel, not the present version. I am debating saving the sheets out to individual CSV files, then importing them through Microsoft Query, but am hoping for a more elegant solution from this forum.

View 6 Replies View Related

Select Multiple Sheets And If Value In Cell Is True Then Copy Values In All Sheets And Hardcode Data

Feb 26, 2012

I have a workbook that updates from external source and creates sheets depending on a cell range.

I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets

What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far

I get compile error here ........Sheets(ArrSh(1)).Activate

Also need it to work for all the other rows.

Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate

[Code] ......

View 2 Replies View Related

Run-time Error '1004' Method 'Add' Of Object ' Sheets' Failed Adding Multiple Sheets

Aug 9, 2007

I have been running a simulation for about 18 hours now and just received:

Run-time error '1004':
Method 'Add' of object ' Sheets' failed

I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:

ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)

The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...

View 9 Replies View Related

Printing Multiple Sheets In Workbook With Hundreds Of Sheets

Feb 18, 2014

I have a work sheet named "Main_List"...In column D starting with "D2" I would like to list worksheets that I would like to have printed via VBA.

The workbook has several hundred worksheets and I would like to list in column D only worksheets that I would like to print with VBA code.

View 3 Replies View Related

Copy From Multiple Sheets (26), PASTE To 1 Sheet From 26 Sheets

Dec 26, 2009

I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.

I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.

View 9 Replies View Related

Running Multiple Modules Across Multiple Workbooks With Multiple Sheets

Feb 19, 2012

I've got data being scraped from a site, putting 1 new workbook in a folder each day

each workbook has 40 sheets in it.

i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.

ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..

is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?

View 5 Replies View Related

Counting With Multiple Criteria

Oct 7, 2009

I have common tasks where I need to sort through large amounts of data to receive a total number of line entries matching multiple criteria in different columns. I've attached a Workbook with a simplified example. On STATS!B1 I would like to total, from DATA!, all lines which have "1A" in A:A and any of the following in E:E (RP, SAO, AE, RSNR).

View 4 Replies View Related

Counting Multiple Options

Dec 6, 2009

Having great success at using Countif and Sumif in totaling values across two sheets, but at the risk of sounding like a dummy I would like a suggestion as to which formula I should be using for this basic little function;

I have a column on a sheet with dropdowns offering 4 options; Active, Cancelled, Completed, and Suspended. I'm using Countif to total the number of "Active" but I also need to total the number of "Canceled", "Completed", and "Suspended" under the one heading, as if they all meant the same thing.

View 6 Replies View Related

Counting Multiple Conditions

Nov 10, 2008

My worksheet contains a database of systems that have been installed since 6/08. Each system has its own unique serial number, however, this serial number appears more than once.

I'm trying to count the number of systems that have been installed since 9/08/08 but I do not need Excel to count each s/n entry, I only want it to return a count per s/n once to get a total of all installations since this date.

I have attached the sheet in pk zip format.

View 8 Replies View Related

Counting With Multiple Variables?

Jan 23, 2013

I'm trying to count a range but I have multiple criteria which I would like to use, I have attempted to use countif/sumproduct with no avail. The data is spread over two columns, one contains the rank of various employees and the other is the number of hours they have worked. I am trying to count how many employees fall in to set hour ranges.

View 2 Replies View Related

Counting Using Multiple Criteria

Jan 12, 2010

I am trying to count the total number of oil rigs which have revenue in column H (postive or negative revenue, just not zero), revenue in column L, and have the owner name "Transocean" in column B. I'm trying to use this SUMPRODUCT formula but it's not working.

=SUMPRODUCT(--('Golden Source'!H2:H6280),--('Golden Source'!L2:L6280),--('Golden Source'!B2:B628="Transocean"))

View 9 Replies View Related

Counting With Multiple Conditions

Nov 5, 2009

I am looking to get a formula that count the number of rows that match some
criteria. The sample table is listed below. I would to have a formula
calculate the # of rows in which values in column B (>=3 and <=3.9) along with values in

Column C = 0

IDRATINGAMOUNT(formula match)
003044.0 (5.0)
002992.5 0 x
000692.5 2.0
013804.0 0 x
017293.0 0 x
017832.5 0 x
019342.0 (1.0)
019013.8 (1.0)x
014823.0 (1.0)x
020762.5 (1.0)

COUNT346

B>=3, B<=3.9 = 3 rows
C=0 = 4 rows
Overall = 6 total rows

But if you combine both criteria, there are 6 rows that match. I'm having a
difficult time figuring our how to write the formula for this.

View 2 Replies View Related

Counting Cells With Multiple Criteria

Jan 23, 2010

Column A contains "Power","Instr","EHT", Column AJ contains "25M32", "25M35","25M39"and the a date is in column AE. I need to count how many time the date appears in AE if column the cell in A is "Power" and the cell in AJ is "25M32".

View 2 Replies View Related

Counting Multiple Text Conditions

Nov 7, 2008

I am working on a spreadsheet that contains multiple text conditions that I need to keep count of. I would like to try the countif but I don't know if this will work. Here's a summary of my spreadsheet

Column F in my spreadsheet contains 7 different regions (all text).
Column D contains an open or closed option that is not validated.

In my totals section of the spread sheet I need to count each region that meets the "closed" criteria.

View 3 Replies View Related

Counting Each Occurrence Of Multiple Values

Dec 2, 2008

I'm working on an attendance sheet, and have allocated certain letters for related occurrences. For example, V=Vacation day, S=Sick day, B=Bereavement, etc. Over a two-week period (eg - D19:D32), I want to total the number of times one of these values has been used, and add to work hours. Where an employee will enter 7.5 in D19 to indicate hours worked, they may instead enter a 'V' for a vacation day, and have 7.5 hours still added to their total hours in the pay period. I have a formula that works, but it is so incredibly long that I'm thinking there must be an easier way.

Right now, I'm using the following:
=SUM(D19:D32)+(COUNTIF(D19:D32,"V")*7.5)+(COUNTIF(D19:D32,"Vh")*7.5)+(COUNTIF(D19:D32,"S")*7.5)+(COU NTIF(D19:D32,"Sh")*7.5)+(COUNTIF(D19:D32,"H")*7.5)+(COUNTIF(D19:D32,"B")*7.5)+(COUNTIF(D19:D32,"A")* 7.5)

View 3 Replies View Related

Counting Multiple Values In One Cell

May 2, 2009

I have a spreadsheet with data from a survey. There are 375 respondents (rows) and 26 questions (columns). I use COUNTIF in order to figure out the frequency of responses and SUMPRODUCT to do some finer analysis. Here's my problem, some of the questions have multiple valid responses. In those cases the data was entered into the same cell. So I have a column that looks like this:

1,4
2
1
3,12
1,11
8,
3,14,9

I can't figure out how to count the number of "1"s. It would be much easier if the responses could only be 1-9, but unfortunately they can be 1-20. So far, I've come up with:

View 3 Replies View Related

Counting Unique Across Multiple Columns?

Nov 23, 2011

=SUM(IF(FREQUENCY(IF(B2:B20="Temp",MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)+1),1))

The works when referencing two columns. I need a total count of unique across three columns.

Sample Data

A | B |C
Jason | Temp | 1
Jason | Temp | 1
Jason | Final | 1
Kim | Temp | 1
Mary | Temp | 2

My summary table has the unique values of C in first column and the unique values of B as headers. I want the unique counts as the data.

Temp | Final
1 | 2 | 1
2 | 1 | 0

View 5 Replies View Related

Counting Multiple Occurrences With Various Arrays

Jan 9, 2008

what I'm looking at doing is counting the number of reccuring unauthorised absences in a list. We have the persons name in column B, the absence type in column C and the date in column E. Basically I need to send out an AWOL notice when 5 days of unauthorised absence for the person in column B has occurred, so I need some sort of indication that this has happened in order for me to stick some conditional formatting in there to flag it.

View 9 Replies View Related

Counting Items With Multiple Criteria

Sep 8, 2006

In column A, it contains the monthly salary data of each employee, then in column B, it contains the classification level data, Like Grade A, Grade B and Grade C. In this exercise, it want to find out the statistic as follows:

1. How many employee's monthly salary is below 10K and their classification level is Grade A, B or C
2. How many employee's monthly salary is above 10K and their classification level is Grade A, B or C

So any formulars can do that in instead of using the sorting method?

View 3 Replies View Related

Counting Number Of Multiple Entries

Jun 6, 2007

I am trying to count the number of indentical entries in my spreadsheet. I got the conditional formatting to color indentical entries that have more than 2 entries. I need to know how multiple entries are in my spreadsheet. I am using =COUNTIF($A$1:$A$10000,A1)>2.

View 9 Replies View Related

Counting Cells With Multiple Conditions

Aug 14, 2008

I have a large list of items and I want to be able to count and sum up the cells based on multiple conditions. Lets just say for example I have a list like this, also the list updates and adds more rows for more information, so its not confined.

xxx Open
xxx Complete
yyy Complete
yyy Open
xxx Complete
xxx Open

I want to be able to count how many xxx are Open. I assume it involves either CountA, CountIF, If, And but I keep getting errors when It searches for text. I can use CountA and have it search for text of a single columb just fine, but I cant do contitions based on multiple columbs with just a cell formula.

View 9 Replies View Related

Counting Unique Values With Multiple Conditions

May 7, 2014

I need counting unique values with a number of given conditions. I need this to be a formula so that I can just add data to my spread sheet and it will automatically calculate.

The formula I have is:
SUM(IF(FREQUENCY(IF(A:A=Z1,IF(B:B=Z2,IF(C:C>Z3,IF(D:D<=Z4,G:G)))),G:G),1))

The goal is to count all of the unique value in column G:G that meet certain criteria (have ending times greater than Z3 and beginning times less than Z4, etc.). Once I get the formula to work, I'll simply drag it down to have it calculate for similar lightly changing criteria (like different beginning and ending intervals). However, the formula doesn't seem to be working. It's only giving me zero values (I have "ctrl-shift-entered").

View 9 Replies View Related

Counting Merged Cells As Multiple Values

Aug 15, 2014

I have a spreadsheet, similar to the one attached, that monitors deliveries. The reasons for deliveries not being made on time are merged across to have one reason per delivery vehicle.

I am trying to count the number of times a reason occurs, but utilizing a COUNTIF function it will only count a merged cell as one count, rather than per each delivery.

Merged Count if examples.xlsx‎

View 8 Replies View Related

Counting Unique Values With Multiple Criteria?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved