Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

countif same range across multiple tabs

Hi I have a countif formula as follows
=COUNTIF('Jan 10'!B:B,A2)

I have numerous tabs labelled with every month of the year "Jan 10" "Feb 10" etc.

I want the formula to look up the same range on every tab but when I put this formula in it gives a "#value" error.
=COUNTIF('Jan 10:Mar 10'!B:B,A2)

Does anyone know of a way to do this thanks!


Post your answer or comment

comments powered by Disqus
I have a workbook with multiple worksheets (separated by month).

Each worksheet looks like the below

******** ******************** ************************************************************************>Microsoft Excel - 2006 Scorecard Performance Data.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutF3=
ABCD1EmployeeCScoreFScorePscore2693083.2471.6413699623.3442.2114698283.7742.815699093.7423.54166997033.9717699733.06541may 07 pts*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I would like to count how many time a particular "employee number" shows up across multiple worksheets. (note employee numbers are unique per worksheet).

I just can't figure out the format to use countif range across multiple worksheets. Is this possible, or should I pile it all onto one worksheet?

Any help would be much appreciated.

Posted: Thu Jan 19, 2006 12:51 am Post subject: Adding same cells
across multiple worksheets

--------------------------------------------------------------------------------

I have a file with 10 worksheets, each of which contains a P&L
statement for each different department within the company, and another
worksheet that rolls all the department totals into one consolidated
P&L.

The consolidated worksheet adds the balances of each individual
worksheet to calculate the company total.

If each worksheet is called "Dept1", "Dept2", etc., and the
consolidated worksheet is called "Total", my formula in "Total" for
each line was this:

=+Dept1!A5+Dept2!A5+Dept3!A5...and so on.

I then changed it to this because it's much shorter:

=SUM('Dept1ept10'!A5)

The only problem is that there are other worksheets in this file as
well, and the users tend to move the placement of the worksheets around
to suit their needs, which would obviously make the second formula
inadequate if they moved one of the department worksheets out of the
listed range.

Is there a formula that "locks" the worksheet names so that all ten
worksheets will be included in the total, regardless of where they are
moved within the file?

--
LACA
------------------------------------------------------------------------
LACA's Profile: http://www.excelforum.com/member.php...o&userid=30381
View this thread: http://www.excelforum.com/showthread...hreadid=502742

Posted: Thu Jan 19, 2006 12:51 am Post subject: Adding same cells across multiple worksheets

--------------------------------------------------------------------------------

I have a file with 10 worksheets, each of which contains a P&L statement for each different department within the company, and another worksheet that rolls all the department totals into one consolidated P&L.

The consolidated worksheet adds the balances of each individual worksheet to calculate the company total.

If each worksheet is called "Dept1", "Dept2", etc., and the consolidated worksheet is called "Total", my formula in "Total" for each line was this:

=+Dept1!A5+Dept2!A5+Dept3!A5...and so on.

I then changed it to this because it's much shorter:

=SUM('Dept1:Dept10'!A5)

The only problem is that there are other worksheets in this file as well, and the users tend to move the placement of the worksheets around to suit their needs, which would obviously make the second formula inadequate if they moved one of the department worksheets out of the listed range.

Is there a formula that "locks" the worksheet names so that all ten worksheets will be included in the total, regardless of where they are moved within the file?

It would be a nice feature in excel if you wanted the same header across
multiple tabs to be able to select that as an option instead of
copying/pasting in each tab manually.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...ic.excel.setup

I was wondering if its possible to print the same range on multiple sheets in
one command. (Rather than doing it in two parts by setting the print area
first and then printing the sheets)

for instance, i'd like to print the area named by the range name "March" on
Sheet1 and Sheet2. Is there a way to do this?

Thanks in advance.

Hi everyone,

At my job my team uses a template for work that has our company logo. We have a bunch of these template in progress and need to change the logo across multiple tabs in each workbook.

I've tried to do so by just selecting the tabs and going to "header/footer", selecting "Picture", then "Replace" and I can replace the picture but the problem is when I go to the other tabs it screws up the page setup and print area formatting for all of the other tabs selected except for the first tab.

Is there any way to change the picture in a header across multiple tabs withouth having this margin issue or do I need to use a macro to do this?

Thanks!

How do you make a chart reference the same cell but across multiple tabs? Example I have a year worth of tabs each called MMDDYYY, I want to chart a value that appears in the same cell on every tab, to get a trend for that cell value.

THanks so much.

HI,

Trying to count the same value across multiple ranges in multiple sheets. I'm just getting #VAULE! as a return.

Any assistance with this code?

=SUMPRODUCT(COUNTIFS('Q3Opp Data'!N1:N9181,'Q4Opp Data'!N93,'Q2Opp Data'!N1:N7870,'Q4Opp Data'!N93,'Q1Opp Data'!M2:M7726,'Q4Opp Data'!N93))

Many thanks.

My workbook contains 30 worksheets with identical layout, named 01 to 30. I'm trying to count the number of sheets in which the value of cell B12 is less than 0.6, using the following formula:

=countif('01:30'!B12,"

Hey all. 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.

Code:
 
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.

Any help on this would be appreciated.

I have a series of about 30 workbooks that each contain the same number of worksheets. All of the worksheets share the same name across all of the workbooks and the ranges within each of the worksheets are formatted the same.

I'm looking for VBA code that will allow me to copy the same rows from the same worksheet across all workbooks and paste those rows in subsequent fashion in one workbook.

For instance, say the 30 workbooks all contain one worksheet called "Customer Purchases". I'd like to copy rows 1 and 35 to 44 from each "Customer Purchases" from each of the 30 workbooks and paste these rows into one destination workbook with 30 tabs with each tab representing a respective workbook.

In other words, "Sheet1" in the destination workbook would contain rows 1 and 35 to 44 from the "Customer Purchases" worksheet in workbook 1, "Sheet2" in the destination workbook would contain rows 1 and 35 to 44 from the "Customer Purchases" worksheet in workbook 2, and so on.

Does anyone know how I can do this?

I've searched the forum and found a similar question, but alas, it had no posted answer. Perhaps someone has come up with an answer since the original poster asked their question, so I'll ask mine and hope for the best

I have a series of tabs where I'm keeping up with stats for a soccer game. Players' Names down the rows and stats (shots on goal, shots made, etc.) across the columns. I have a separate tab for each game played.

I have a summary tab, where I'm totalling the stats for the entire year. Currently I'm using a simple SUM formula. SUM('Game1:Game10'!B7) for example for the player on row 7. This works great as long as I have the Players' Names in exactly the same row on each tab. But as players come and go, this is beginning to be an annoyance.

I attempted to change my SUM formula to an SUMIF formula. But SUMIF doesn't appear to like using a range of tabs.

=SUMIF('Game1:Game10'!A:A,$A7,'Game1:Game10'!B:B) returns #VALUE!

Am I attempting to do something that can't be done or do I have an error in my formula?

Any other suggestions for how to accomplish my goal?

Good morning all.

Not sure where to look for help on this issue as no matter what I type in to help files, nothing seems to pop up.

I have a statistics page in a file that captures data from 12 data sheets.
I want to be able to look for a value over the entire 12 sheets.

Column D on all 12 sheets have dates.
I would like to return a count if any cell in column D across the 12 sheets has a specific value.

ie .. how many cells have a value of 1 Sept.

Any ideas?
Have tried VLOOKUP and COUNTIF .. but cant seem to get them to work across multiple sheets.

HELP !!

Hi,

I am trying to do a sumproduct over multiple tabs. I have been looking at a number of forums but still cant seem to find a good answer to this problem.
I am using excel 2007 below is the formula that has come closest to working...but doesn't.

Basically I have a variable number of tabs plus a TOTAL tab, I am trying to do sumproduct based on 2 criterion the first is a text match where the value being matched is located in B2 of the TOTAL tab this is checked against cell A1 in all other tabs, then the second match is checked against dates so C7 contains Dec 11 (in date format MM/DD/YYYY) this is checked against range C7:AB7 on all other tabs when both matches are found I want to sum values on all other tabs in the range of C8:AB8.

SheetList is a Named Range, where I have a dynamic range set up on another tab that lists all tabs in the workbook.

Any one have an idea where I'm going wrong? Or is there a better way to do this?

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&SheetList&"'!$A$1"),,,))=$B$2),--(N(OFFSET(INDIRECT("'"&SheetList&"'!$C$7:$AB$7"),,,))=C$7),--(N(OFFSET(INDIRECT("'"&SheetList&"'!$C$8:$AB$8"),,,))))

I've got a file that has many, many tabs in it. The tabs are all formatted the same, and I'd like the same row on each tab to be the header row and repeat at the top if that particular tab requires multiple sheets for printing.

When I select multiple tabs and try to apply it, the header row repeat option is grayed out. Is there another way around this?

Thanks!

In Excel, I have 144 tabs within the same worksheet. I need to use the Edit
> Find option to find a specific product id within each worksheet. For an
example, product ID: ABC. How can I use the "Find" option across all tabs at
the same time? Thanks

I have a column of data with a variety of identifier codes. In each row next
to the ID codes is data. There are several tabs within my workbook with
similarly constructed data bases. Can I use Sum Product to summarize the
data across the multiple tabs that match each of the id codes? If not, is
there another way?

starting point - i have an xls book with multiple worksheets, each with same formatting.
looking for formula to search for specific name across multiple worksheets (some form of vlookup?)
once that name is found, i want to sum the values returned from specific column.

for example: summarizing baseball team stats for each player based on a worksheet for each game.
trying not to have to keep going back and forth updating each individuals stat from each game.

Just wondering....without using macros, is there a way to use the autofilter function across multiple tabs? For example, I have 3 tabs with identical information in columns a-c and have autofilters set over said columns on each tab. I'm looking for a way to update the filters on all 3 tabs simply by changing my selection on tab 1.

Ideas?

I have multiple tabs in a spreadsheet (each one signifying a date). I want to extract the same cells in each tab onto a summary sheet. Is there an easy way to do this without any VB?

I would like the summary to look something like this:

Cell A2 (This is the date) Cell R3 Cell D4 Cell D5 Cell D6

Hi,

Consider me a raw beginner (actually I'm a little more advanced than raw beginner, but not much).

I'm looking to create a pivot table report from data that exists in multiple tabs in my workbook. The data is similarly formatted.

It is a workbook of customer issues. Each customer has its own tab. The column structure is identical, with headers on the columns, but the rows don't have headers.

For illustration purposes, each row is a customer issue. The first columns are:
tracking numberissue descriptionissue categoryissue sizeopen or closedCustomer name (same as the tab name).
When I try and put a pivot table within one customer tab, It works exactly as I expected and I can get the results I'm looking for (such as count by issue category, count by issue category by issue size).

What I want, and can't figure out how to do, is to add a new summary sheet with a pivot table that does the same thing I described above, but includes the data in all the tabs. I would have the highest order sort be customer name.

Suggestions are most welcome and would be gratefully received.

Thanks,
-Mike

ps: I did a search but the closest I saw involved SQL and multiple files. I don't know SQL and was lost trying to figure this out.

Hi,

Still not solved this one. In summary I need to sum cell B3 across specific tabs named "New wk 1" to "New wk 25".
So far I have =IF(1,Array,"sum('"&$J$1&":"&$J$2&"'!"&CELL("address",$B$3)) where J1="New wk 1" and J2="New wk 4"

This is giving me the correct answer. Then I need to replicate this formula for other cells in the summary page to give me cell c3,d3,b6,c6,d6 etc.

This formula only works when the desired cells are in column B? I.e I use =IF(1,Array,"sum('"&$J$1&":"&$J$2&"'!"&CELL("address",$C$3)) and it still gives me the sum of column B?
Am I missing something here?
J1 and J2 are formulas linked to another cell. Ie type in Jan somewhere on the spreadsheet to get "New wk 1" in J1 and "New wk 4" in J2. So when I type in Feb it changes the J1/2 cells to "New wk 5" and "New wk 8".

But I can't seem to find a consistent way of using this =if(1,array formula across all columns...... I know it works as it does in column B. But how would I get this to work for the rest of the columns?

Thanks

Is there a way to use the Find tool (Ctrl + F) to search across multiple tabs (they may be called worksheets) in a single workbook?

Thank you!

I am trying to combine the SUM(Start:End!A1) functionality with SUMIF.

Basically, I am trying to sum a range across multiple sheets if it meets certain criteria. For example, if the range on sheets 1,2,3 begins with 2, then add it up. Anyone have any ideas how to do this without macros but still hve it be dynamic to capture the addition of new sheets?

Let me know if you need more detail.


No luck finding an answer? You could always try Google.