COUNTIFS Across Multiple Worksheets
Jan 24, 2014
I am having a problem getting my formula to return a result. The basics are that I would like a count of a specific set of data in a section of a workbook. So in column 'A' I am searching for 'TextA' but this also has to have 'TextB' in column C. The problem is I have 12 consecutive monthly worksheets I would like to pull this data from and I only really want to write one formula. I can (and started to) use the countifs function plus countifs function etc referencing each worksheet individually but this seems long winded.
I have read on this forum that I can use sumproduct combined with countifs but I can't seem to get it right. I have listed my formula for a single sheet countifs function below, but I don't know how or if to include the sumproduct function to reference the rest of the monthly worksheets. The other thing I should mention is that there are other worksheets too, so it would need to be specific to this range, but they are consecutive within the workbook.
View 4 Replies
ADVERTISEMENT
May 18, 2009
I created the following in Excel 2007 but need to come up with a replacement formula in 2003.
=COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp")
With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells.
In terms of replacing it i have tried to use something like =SUMPRODUCT(--('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.
View 2 Replies
View Related
Nov 11, 2009
I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,
View 9 Replies
View Related
Jan 4, 2012
i have 10 sheets in my workbook and i wanted to do countifs on every sheet for column a so i have created 2 more sheet one called start the other call end and moved them start before sheet1 and end after sheet 10
this is the formula i was trying to use =COUNTIFS(Start!:End!A:A,finished!A3)
View 6 Replies
View Related
Jan 23, 2014
I am currently trying to create a Formula which will count my data on a range of conditions.
Here is an example of my data:
A
B
C
1
Active
4 - High
3 - Medium
[Code] .....
This data then continues on for a number of rows. Basically I want to count the cells which contain "Active" or "Tolerated" in row A which also has a score of 4 or 3 in column B and also a score of column 3 or 2 in column C. So the formula should count Rows 1 and 4 in the above scenario.
The problem I have is the data in B and C contains text as well as a number so I assume there is not a way to use a greater or lesser than operater? and instead I will need to add a condition to just search the cell which contains the number 4 or 3 in column B for example.
I have had a go at this but am not getting very far. Would COUNTIFS be the best formula to use for this?
View 9 Replies
View Related
May 20, 2014
I would like to count the number of values in column B IF the value in Column A is equal to a value from a range of cells.
I would like the formula to be in cell E7. And the range of cells containing the values is in column D.
Here is my workbook Book1.xlsx
View 1 Replies
View Related
Oct 23, 2013
This is the function I have now: =COUNTIFS(Sheet1!A:A,Sheet2!A2,(Sheet1!E:E),"*") and it works great, counting every cell with a value in column E.
When I make criteria range 3 another column (criteria also being"*") my numbers actually start going down. I think it might be averaging the two columns out or something but I just want it to count all the cells with values in it from multiple columns and give me a total count.
I have 4 columns total that I want to be able to get a total count from.
View 3 Replies
View Related
Jul 8, 2009
I want to count the number of occurences involving 3 columns of a spreadsheet, but one of the columns is a date/time field, and I only want to analyse the date.
Example:
A B C
John 1 02/07/2009 09:45:10
Peter 1 03/06/2009 10:14:12
Fred 1
Peter
John 1 02/07/2009 09:45:10
How can I ask how many occurences of John are in column A, with a 1 in column B, with the date 02/07/2009?
View 9 Replies
View Related
Apr 14, 2014
I have multiple rows of data per each column. I'm trying to count rows that match a value that ISN'T any of the expected values in column A as well as a certain value in column G. This is in case future data is introduced that doesn't match the existing codes.
I'm trying to count all rows that DON'T have a value in column A beginning with PUA or P9V, so far I am trying this:
=COUNTIFS(A:A,"<>P9V*""<>PUA*",G:G,"P")
But it only seems to work on the "<>P9V" part. When I put P's in the column G of which the column A matches PUA*, it still counts the cell. I've tried a couple of AND variations but none are working.
View 2 Replies
View Related
Feb 26, 2014
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
View 8 Replies
View Related
Oct 10, 2009
I am unable to get the correct results using Sumifs and Countifs function. Below is an example:
If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9)
I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0.
BCDEF2CountryMain Conseq.Other conseq.Product Type Amount 3AustriaInternalNoneInt2B $ 278 4AustriaExternalNoneExt3C $ 900 5AustriaExternal-8A $ 2,388 6NorwayInternal-MV $ 567 7AfricaExternal-SY $ 1,234 8AfricaInternalNoneIntJN $ 908 9TOTAL $ 6,275
Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9)
And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1.
Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.
View 9 Replies
View Related
Feb 16, 2014
I am trying to populate some results using countifs formula with multiple criteria. below is the formula am using and the last two criterias are dates
=COUNTIFS('Unproccessed CIT'!$B$5:$B$411,Summary!$A$4:$A$35,'Unproccessed CIT'!$G$11:$G$417,"<>"&"closed",'Unproccessed CIT'!$A$5:$A$38,">="&$C$2,'Unproccessed CIT'!$A$5:$A$38,"<="&Summary!$E$2)
Results:
#VALUE!
View 3 Replies
View Related
May 14, 2012
I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
View 4 Replies
View Related
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
Aug 26, 2013
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies
View Related
Jul 12, 2014
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
View 4 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
Jul 28, 2013
I'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
View 5 Replies
View Related
Mar 27, 2014
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
View 8 Replies
View Related
Aug 18, 2007
I have an excel workbook with 8 worksheets. Each worksheet has vertical columns (approx 250 columns per sheet) of numeric data. Is there a function or macro that will combine all of this data into one vertical column without having to individually cut and paste each one into the new column?
View 3 Replies
View Related
Sep 5, 2008
I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
View 9 Replies
View Related
Jan 28, 2009
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
View 4 Replies
View Related
Dec 27, 2013
Code to merge worksheets from different workbooks stored in different location.
I have a sheet called "Master" in all the workbook I want to combine.
I have a unique password for all the workbook as well.
All the workbooks are stored in different folder location.
I would like to do a paste special values when the consolidation takes place.
View 1 Replies
View Related
Sep 7, 2006
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
View 4 Replies
View Related
Aug 10, 2007
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet.
The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets.
To reiterate, here's what I am trying to do.
Step 1. Create a summary sheet.
Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet.
Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet
I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data
I am trying to do this using macros (within an add-in)
I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.
View 4 Replies
View Related
Nov 21, 2006
I need a summary page in a workbook that will pull data from multiple workseets with mulriple criteria.
I have treid sumproduct, but that doesn't seem to be working when I select a range from multiple workseets.
View 9 Replies
View Related
Jan 8, 2010
I have a macro that I presently use with just one worksheet. I now need this same macro to do the exact same thing on additional worksheets (in the same workbook) that I plan to add. These new worksheets will have the exact same format, structure etc., only the data that my users enter will be different.
Question: Is there some simple way to modify my existing macro so that each time it runs it performs its functions over multiple worksheets?
(I'm guessing that it has something to do with the "worksheets.active" code but I can't figure out how to get the right syntax to get that to work.)
View 9 Replies
View Related
Nov 11, 2006
I have 4 sheets that use the same template.
Now user has option to add new Rows to it.
There are few predefined row layouts (using diffrent lists as column values).
This is code that i tryed to use:
Only adding new row and insertin its name (i didnt try formating cells yet since i cant get this simple stuff to work).
Dim ActiveSheetName As String
ActiveSheetName = ActiveSheet.Name
Sheets( Array("Week One", "Week Two", "Week Three", "Week Four")).Select
Range("A14").Select
Selection.End(xlDown).offset(1, 0).Select
Selection.EntireRow.Insert
Selection.Value = TextBox1.Value
But this code only enters new row in first sheet (Week One).
Other sheets are unchnged.
TextBox value is enterd in 4 sheets.
View 3 Replies
View Related
Oct 6, 2007
I have the following code for adding and naming worksheets from the default setting of three worksheets for a new workbook. However the first array element, "D1" is skipped and a blank sheet is inserted at the end.
Sub AddWorksheets()
Dim Tabs As Variant
Dim I As Byte
Tabs = Array("d1", "O1", "O2", "H1", "H2", "L1", "L2", "C1", "C2", "V1", "V2", "AC1", "AC2", "M1", "M2", "T1", "T2", "P1", "P2", "SR1", "D1", "D2")
Sheets.Add , Count:=19
For I = 1 To 22
Sheets(I).Name = Tabs(I)
Next I
End Sub
View 9 Replies
View Related
Jul 27, 2007
I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.
For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11
Is there a faster way to do this?
View 6 Replies
View Related
