# Adding Multiple Cells From Multiple Sheets With Sumif Function

Jan 20, 2009
I'm trying to put together a spreadsheet that tracks disc capacity increases, affected by any incoming projects. I've managed to do so for one project, but would like to for up to 10. The way i've designed the solution (i'm sure there are far more elegant ways, but hey) is thus:

A forecast worksheet keeps track of a grand total, taking information from sheets P1 -> P10 (being projects 1 to 10). I am unable to figure a way to add up all the increases from all 10 project worksheets with one succinct formula. What I use so far is: ='P1'!C83+SUMIF('P1'!E82,"=2009 - Q1",'P1'!D82) ..................

View 5 Replies
ADVERTISEMENT
Jul 19, 2007

How do I get a function to check cells on multiple work sheets.

For example this function searches for the word "hello" in cells, A1 to A50 and then adds up the number in the corresponding cells where "hello" is found from C1 to C50:

=SUMIF($A$1:$A$50,"=hello",$C$1:$C$50)

Two questions:

01) How do I search the same cells in two further work sheet, "Sheet2" & "Sheet3"?

02) Is there a way to search every cell in an entire work sheet?

View 9 Replies
View Related
Feb 26, 2013

I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.

I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.

View 4 Replies
View Related
Jan 18, 2007

I am performing a sumif to look to see if a number starts with #, then another to see if the the class is text, both are fine and both return values of 1 if it meets the criteria. My problem is i then need to add a sumif (or something im not quite sure wot) to add the cost if the number begins with a # and the class is text (so baically if both the other sumifs = 1).

View 9 Replies
View Related
Apr 21, 2006

I have the following formula that I am trying to use but the result is #VALUE:

=SUMIF(Sheet1:Sheet9!B1,"PMI",Sheet1:Sheet9!A3)

The formula should look in cell B1 of each sheet and if it is equal to "PMI"

sum of the value in cell A3 in each sheet.

View 14 Replies
View Related
Sep 28, 2012

I have got a workbook with multiple sheets, each sheet is in the same format but with different products across the top. The below formula sums the relevant columns by looking for the column with the relevant product code then sum accordingly. How do I amend the formula so that it does the sum for multiple sheets. I could just copy the formula for each sheet but their must be a better way to do this.

Code:

=SUMIF('FM'!$A:$A,$A2,INDEX('FM'!$A$1:$J$34,0,MATCH(Summary!B$1,'FM'!$1:$1,0)))

View 1 Replies
View Related
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
Jan 3, 2012

I am trying to use the SUM IF Array formula to sum a group of numbers that fall under a heading of reference numbers over several sheets of data. For example purposes lets say my spreadsheet looks something like the below.

A

B

C

D

E

1

2600000248391

2600000393805

[code]......

The first two digits of the heading numbers are the criteria I am trying to use to separate and sum the data. For example I need to sum the value of the data below headings that falls between 1400000000000 and 1499999999999.

For the example above I used the below formula for the current Sheet and it works fine.

{=SUM(IF(A1:E1>="1400000000000",IF(A1:E1="1400000000000",IF(Sheet1!A1:E1,Sheet2!A1:E1

View 5 Replies
View Related
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
Jul 11, 2014

I have made a text box in one sheet where the user will enter the text that is to be added, and it works! How do I get the exact same text box to be entered into all of the sheets in my workbook?

[Code] .....

View 2 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
Nov 18, 2013

I have multiple sheets (Sheets "A", "B", "C", etc) with the same structure and formatting. For these sheets ("A", "B", "C", etc.), I want to create dropdown lists in column F (cells F2:F100) based on values from a different worksheet (Sheet "DropDown", Cells "B2:B130").

I saw a previous post [URL]... which had a single sheet example. I am trying to come up with vba code for multiple sheets and so far it's not working.

VB:

Dim wkst As Worksheet

For Each wkst In ThisWorkbook.Sheets

ThisWorkbook.Names.Add Name:="listdata", RefersTo:= _ "=dropdown!$B$2:$B$130"

With wkst.Range("F2:F100").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=listdata"

End With

Next

End Sub

I am a beginner with vba coding

View 2 Replies
View Related
Jan 8, 2014

Below is my function.

="Profit $"&SUM(IF(Sold!D15:D8998<>"",IF(MONTH(Sold!D15:D8998)=A4,Sold!H15:H8998)))

I would like to edit this function to also count profit only if Cell A15:A8998 in my Sold sheet has the words "Shipped". The text value Shipped is not a text value I entered, it is automatically written based on a Vlookup formula I created.

View 2 Replies
View Related
May 9, 2014

It is entered in Column B of the sheet Round 2. Basically what I want it to return is the lower price from two different sheets looking them up by part number. Also I guess something I didn't think of is that the HD Final Sheet will not contain all the parts...while the other sheet Round 1 will have all. In cases where the part number is not found on the HD Final sheet I want it to put the price from the Round 1 sheet.

View 14 Replies
View Related
Jan 19, 2009

My sheets are all categorized by date, and I want to be sum individual cells on each worksheet on one final summary worksheet. For example, if cell A1 on each sheet was units sold and I wanted to see how many total units were sold between Jan-04-08 and Jan-16-08 my formula on the summary sheet would be:

=SUM('Jan-04-08:Jan-16-08'!A1)

But I want to be able to easily modify what dates my summary sheet shows so I tried using the function:

=SUM(INDIRECT("'"&N14&":"&N15&"'!A1"))

where N14 was the starting sheet and N15 was the ending sheet. It keeps giving me #REF! however and I can't figure out why and don't know if this is something I can fix.

View 9 Replies
View Related
Feb 27, 2009

Can I use the "countif" function using a group of sheets as the range and the criteria on a separate sheet or manually typed in?

I have tried and continue to get the #VALUE error.

OR

Can I do the same thing and perform the "countif" function by using a specific value as the way to count?

View 9 Replies
View Related
Dec 1, 2006

I am trying to use the MODE function over multiple sheets within a work book, however when using =MODE( 'First Page:Last Page'!K5). the formula returns "#REF!". this should be returning 7, 8 or 9. I have already checked to see if the range is wrong, but when I changed the function to SUM, AVERAGE, etc they work fine....

however due to the need for non-excel users to easily add extra pages, adding each page individually would be a pain (and would mean I would need to train the staff in Excel)

View 4 Replies
View Related
Feb 4, 2010

Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:

=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)

The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.

With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).

View 14 Replies
View Related
Nov 5, 2008

Here is what I have In cell H4 I put the following code =B5:G5

But when I add number in thous cells nothing happens. I want cell H4 to keep a running total of cells B5 through G5 so as I add numbers to the cells I would like the total in H4 to change.

View 2 Replies
View Related
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
Feb 18, 2014

I have a spreadsheet with 4 columns.

A, B, C, D

I want to multiply A*B and then add that to C*D

=SUM(A*B)+(C*D)=TOTAL

However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.

Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.

Also - when the total cell is empty I want it blank.

View 13 Replies
View Related
Nov 3, 2008

Let me explain this as best I can:

I have an excel file with multiple tabs on it. Each tab has the exact same format with different numbers. On the last page I want to add cells from each tab and have the sum go to a cell on the last tab.

View 10 Replies
View Related
Sep 3, 2013

This is my text:

Test Name1

Test Name2

Test Name3

Test Name4

Test Name5

Required result is:

Test Name1|Test Name2|Test Name3|Test Name4|Test Name5

View 8 Replies
View Related
Jul 7, 2009

I have column of cells containing entries such as V1, V3 and V7. I'm trying (and failing) to come up with a formula to separate the numbers from the letters and add them together: V1, V3 and V7 would together give 11.

I can use the MID worksheet function to separate my numbers from my letters on a row-by-row basis, but I can't work out how to do the whole thing in one fell swoop. =SUM(MID(A1:A10,2,2)) doesn't work, for example.

View 9 Replies
View Related
Nov 20, 2009

I have data for 500 companies over 10 years, in three criterions: “EPS”, “DPS” & “PX” (i.e. earnings, dividends and price).

For each company I have four lines recording the data, and given a letter code indicating how the change in dividends and earnings have been according to the following

(Examples)

DD-ED = Dividends Decreased & Earnings Decreased

ND-EI = No Dividends & Earnings Increased

As can be seen below this code is present for each company

What I need help with is some sort of lookup function which takes the average of the price changes (which is given in numerical values) only for the companies which code is equal to the code in the reference field.

So something like: =AVERAGE(IF((MOD(ROW(D2:D2353)-ROW(D2)+1,5))=0,IF(D2:D2353"",D2:D2353))) (currently counting every 5th line, since there is 4 lines between respectively PX/DPS/EPS for each company) –But with a constraint indicating only to include the value in the calculation if the codes are the same (e.g. "DI-EI" = "DI-EI").

So that the value, which is currently 20.96% (which now includes ALL price values), would only include those for the respective group (in this case, companies in the DE-EI group).

View 9 Replies
View Related
Oct 1, 2008

I am trying to link 2 excel sheets together and I am wondering if using the SUMIF Function is the best way to do this. I will try to explain and illustrate my position.

First I have a sheet called Master where I import my companies inventory and prices. It contains ALL our inventory. Looks like this:

(Column A) (Column B)

(Item Code (Item Price

(ROW1) THUMBRID $5.00

(ROW2) THUMTUBE $2.00

(ROW3) ZPIN $0.30

Now this master sheet is changing all the time as we import new products and prices all the time. So, this is a "list" where I can sort it by Item Code alphabetically.

Now, I am trying to make another worksheet with a list of what each of our kits contain and there prices. I will call this ThumbPiano. Like this:

Column A: COLUMN B:

THUMBRID $5.00

THUMTUBE $2.00

We have many different kits, that only take a couple of products, and our inventory list is at like 3,400. I want to be able to go into the Master sheet and update an Item price and have it automatically update it in whatever worksheet contains those items. But how can I do this if I add an item into the workseet. Then the cells move and whatever data was in that cell before is not there now.

So, that is why I chose the SUMIF function. Maybe there is a better one? Here's what I tried...assuming the two lists before, and the code Zpin is extra in the Inventory list because we do not use it in the thumb piano hardware pack.

I tried:

=SUMIF([Master.xls]Sheet1!$A$1:$A$3,A1:A2,[Master.xls]Sheet1!$B$1:$B$3)

It works, but then when I save it and close out of it and then re-open the ThumbPiano sheet it asks me if I want to update the sheet. I click yes, and then all the cell says "#VALUE!" "A value used in the formula is of the wrong data type." Please let me know what is going wrong. I know there has to be a way to do this. I just wonder if the SUMIF formula is not the way to link 2 sheets together...but if it isn't please keep in mind that I have this looking through 3,400 parts and I will be adding to them all the time, so it needs to search through all those parts and display the correct price....

View 9 Replies
View Related
Sep 17, 2009

I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.

I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.

View 6 Replies
View Related
Apr 10, 2013

i have data stored like:

col1

XXXX1244 50

XXXX1519 60

XXXX1244 50

xxxxx1111 10

xxxxx1519 65

the last 4 caracters are numbers. I need to test these numbers and sum the corresponding values them in a single cell without adding new column(SUMIF like).

so in the above example I need to sum all ending at 1244 or 1519, therefore the sum showed in the single cell equals 225

to extract from a single cell: =VALUE(RIGHT(D8;4))

I tried to use an array formula but it seems to crash if a blank cell is in the array

View 5 Replies
View Related
Feb 10, 2010

Is there a more efficient method of coding this?

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