Ok here is my dilemma, I am trying to calculate gains and losses by organization. I created a sumifs formula that works on a single page, but it doesn't work on multiple tabs on my summary page.
Here is the data:
HRS DEVIATEDTAILORG0.2OPS-0.3OPS0.8MX0.5OPS-2.5MX-1.5WX-1.5WX
Here is the formula on the same page as data:
=SUMIFS(A26:A40,C26:C40,"wx",A26:A40,"
I can't get this to work. What am I doing wrong? I keep getting a #VALUE! error.
=SUMIFS($G$2:$G$31,$L$2:$L$30,F37,$B$2:$B$30,G$36)
I want to sum the NUMBERS in golumn G if the DATE in column L matches the DATE in cell F37 AND if the TEXT value in column B is equal to the TEXT value in cell G36.
i am trying to us the SUMIFS formula to work out if a rep is below target on selling brands.
I have two worksheets summary and data, Summary worksheet is where i am trying to do the sumif formula against a sales average taking the information from the data worksheet
I have account manager name, brand, sales, DSI
I tryed the below sumifs formula but its coming back as 0
Please could someone have a look and point out the error in my ways
=COUNTIFS(data!$A$2:$A$11,B17,data!F2:F11,"<B2)")
The first count if should count how many times the account manager appears in the brand and the second count is if his DSI on the data worksheet is higher that the target dsi on the summary sheet
I am trying to improve how I write my formula. In this case I have replaced this horribly long formula:
=SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,
"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3006,ChargeOff,"OK")
+SUMIFS(Amount,CAAPartner,$A10,TransactionAccountFunction,"CRT",TransactionSource,
"MC",ClassCode,"PR",TransactionCategory,1,TransactionCode,3031,ChargeOff,"OK")
[Code] ....
with this nice shortened version:
{=SUM(SUMIFS(Amount,TransactionAccountFunction,{"CRT","SCR"},TransactionSource,{"MC"},
TransactionCode,{3006;3035;3031},ChargeOffCredits,{"OK"},CAAPartner,A10,ChargeOffCredits,"OK"))}
I have pieced this together using information on various forums and it appears to work, but I have a question:
What is the difference between using a comma and semi-colon in this new formula?
Here is a sample of the formula I am currently using.
Formula:
[Code]....
This works perfectly for what I was needing but I am adding more criteria and running into a problem. I am rolling together usage for parts with different part numbers but the same function in order to reduce SKUs (as well as capture replaced SKUs) needed. These are located in Columns A:G. what i would like to add is more conditions to where the parts are going. Currently I have one location located in cell B2. To analize my usage further, I want to add another location in C2. So i want my formula to look like this.
Formula:
[Code] .....
when I use this it still only adds up with what matches in B2 regardless of the value in C2. Conversely, I can get the formula to work if I take out the extra part numbers.
Formula:
[Code] ....
Using the SUMIFS function, I could successfully get the expected results from extracting data from 15 base separate excel files (from where the data will be extracted). However, whenever I am re-opening my master sheet(where I want the data to reflect) it gives me #VALUE!
Only after opening all my 15 base sheets, my master sheet fetches data.
I created the following formula and cannot get it the correct result. The return result is only adding column "P' with "275". How can I get it to recognize "276,"278"?
=SUM(SUMIFS('Supplies Only'!N:N,'Supplies Only'!E:E,{"2","26","27","59","86"},'Supplies Only'!P:P,{"275","276","278"}))
SUMIFS formula where it calculates multiple columns as SUM RANGE
Currently I've got this formula:
=SUMIFS(INDEX(Link!$C:$N;0;Mth);Link!$A:$A;LOGIC!B$82;Link!$B:$B;LOGIC!D$81)
The above mentioned formula works great, if I use it for one specific month, but not if I want to have my figures Year to date.
If I want to calculate the sum of multiple columns, and the sum is defined by the month I choose. How should the formula be?
For example: If I choose month 5, I want to calculate column C up till and including G (so only the first 5 columns), and if I choose month 8 it should calculate the sum of column C up till and including J.
I have a below table as below
A B C
a aa 1
a aa 1
a dd 1
a aa 1
b aa 1
b bb 1
b aa 1
b bb 1
c cc 1
c bb 1
c bb 1
c cc 1
d cc 1
d aa 1
d bb 1
d cc 1
When i put the formula
=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"}))
it returns 12 However when i put
=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,{"aa","bb"}))
it returns only 5
I don't want to use multi formula like
=SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"aa"})) + SUMPRODUCT(SUMIFS(C1:C16,A1:A16,{"a","b","c"},B1:B16,"bb"}))
I want to achieve this using SUMIFS only
I will take out the different sheet references and instead show it as if it all on the same sheet.
=SUMIFS(F7:F1000,D7:D1000,C37,I7:I1000,P2:P5))
Im trying to get this too work.
So F7:F1000, is the data to be added together
D7:D1000 = the year
C37 = 2014
I7:I1000 = category, so this has stuff like (food shopping, fuel, car, bills, utilities etc)
P2:P5 = three criterias ([Balance], [Cashback], [Transfer])
Im trying to sum together the values which is 2014, and has the text [Balance] or [Cashback] or [Transfer] in the category column.
I'm trying to sum data based on a the criteria of a date being between a certain range and sorted by a particular name.
I have included a sample sheet with two tabs:
Totals-I would like to get the sum of the hours a person worked in the range of dates being 1/1/2014-1/24/2014 (which is D1 and E1 in the current charges sheet) Current Charges-this is the page that I would like to gather the information from.
Below is a sample of the formula I was trying to use, but I get an error....my interpretation of what I was trying to input is as follows: Sum C2:C34 in the current charge sheet if B2:B34 is greater than or equal to D1 on the current charges tab (which is 1/1/2014) and B2:B34 is less than or equal to E1 (which is 1/24/2014) with A4:A34 in the current charges sheet equal to A2 on Totals sheet.
SUMIFS('Current Charges'!C2:C34,'Current Charges'!$B$2:$B$34,">="&'Current Charges'!$D$1,Current Charges'!$B$2:$B$34,"<="&'Current Charges'!$E$1,'Current Charges'!$A$4:$A$34,A2)
Test Data:
SUMIFS Test Data.xlsx
On the attached work book I have 2 sheets on the total minutes sheets I need to run either a VBA or sumif formula that looks at all the Init columns and if it finds that name then it sums the duration cells corresponding with that name.
Picking log-Rev6.xlsm
I set my range as follows :
[Code]....
This gives me two ranges with multiple columns of data, which should work fine.
Then try to gather my data as follows :
[Code] ........
But this DOESN'T work - it seems like SumIfs doesn't work with a range with multiple columns... ?
The code DOES work if the rng2013/rng2014 ranges have only one column of data instead of more...
Want to apply sumifs formula with multiple rows and columns.
SOURCE DATA
Jan-13
Feb-13
Mar-13
Apr-13
Apr-13
Apr-13
[Code].....
In a workbook I have a tab with multiple columns containing names, activity codes, billed hours, etc. On another tab I have created a budget for which I use the Excel SUMIFS function to check the database in the first tab for relevant entries and add them together in the budget sheet.
Now here's for the tricky part. On the budget sheet the names are not mentioned, only the job titles. So these are grouped together. This means that the first tab shows only names, while the budget tab only shows the function title.
The function I have used thus far is this:
Formula: [Code] ......
The first part refers to the column containing the billed hours, the second part refers to the column containing the names (criteria range 1), the third part refers to a specific name (criteria 1), the fourth part refers to the column containing the activity codes (criteria range 2), while the fifth part refers to a specific activity code (criteria 2).
This works really well as long as the budget tab only mentions the names, yet it only aggegates at the job title level. Would it be possible to use the same SUMIFS function to search the database for, say, all the names that fall within one specific job title? Essentially that would result in this somewhat bulky function:
Formula: [Code] .....
Clearly, Excel doesn't allow this.
Is it possible to use Sumifs to calculate multiple columns i.e. if a summary were able to sum Part No. 742810 O-rings for March it would arrive at 24 O-rings and 20 of the same for April. I tried it using
=SUMIFS(Issues!$F$2:$F$6,Issues!$B$2:$B$6,B8)
(Example) but it seems that Sumifs may have restrictions on the number of columns.
******** language="JavaScript" ************************************************************************>
Microsoft Excel - Parts Distribution.xlsm___Running: 14.0 :
OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL2L3L4L5=ABCDEFGHIJKL1Parts Description Part No.EquipmentMar1Mar5Mar12Mar30Apr6Apr17Apr27May3
[Code] .........
I'm working on a formula for calculating shipment weight per city it goes to per weight scale of the shipment pricelist. Although we have 4 customers with customer codes which we ship with our own trucks and some customers pick up there goods.
Therefore, my sumifs formula currently looks like this:
=SUM(SUMIFS('Freight SH 13'!$R$7:$R$14176,'Freight SH 13'!$R$7:$R$14176,"
I am trying to do a sum of data that contains three requirements - The country has to match either the USA or Canada and then it needs to pull for only a certain month (i.e. Jan, Feb, Mar) and then for that month only pull either Airfare, Hotel, Per Diem, or Car Rental. Basically looking for a way to Sum only the specific category for a specific time frame.
View 9 Replies View RelatedI 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.
I am trying to create a SUMIFS function to sum estimated hours for specific systems that do not have a Completed date. I do not want to sum remaining hours if it has been completed. i.e.
=SUMIFS(Hours, System#,"110-000-00, Complete (Date), ISBLANK)
System #
HOURS
Complete (Date)
110-000-00
0.5
210-000-00
0.5
[Code] .........
I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks. The list is compiled pulling through data from other worksheets. It looks like this...
Sheet Name: MASTER
Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column E = Week ending
Column F to L = Days of the week
Column M to O = Request Type
Column P = Number of days requested for the week
I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...
Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column P = Number of days requested for the year
Column Q = Number of days requested for the summer
I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.
=SUMIF(MASTER!$D:$D,D2,MASTER!$P:$P)
But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q). What I want Excel to do is this................
Is it possible to make multple worksheets from a selection of multiple cells?
This would mean a selection of 10 cells would generate 10 sheets titled with the cell conent.
Ive put some sheet code together that i need copied to 12 sheets (jan to December) in 24 workbooks (each workbook has trhe same sheet names). I dont want to alter the actual content of the Excel sheets, I just need to copy VB code from a template (in VB editor) to the 12 sheets in each of the workbooks. Is this possible to do with VB or do i need some other utility since Im using the VB editor....
View 9 Replies View RelatedExcel 2007
My workbook contains 13 tabs - 1,2,3,...12, and Summary
My data starts on line 4 of every sheet but varies in length - so far the longest goes to line 30.
Rows used on all 13 sheet are as follows:
A - contains facility names
B - contains a two or three letter code
C - contains hours
D - contains dollars
E - contains adjusted rate
On the Summary tab I have listed all the facilites and two or three letter codes. I need to sum column "C" on tabs 1-12 when they match columns A & B on the summary tab. I have tried the following but can't get them to work:
=IF($A5=""," ",SUMPRODUCT(--('1:[12]12'!A$4:$A$50=$A5),--('1:[12]12'!B$4:$B$50=$B5),'1:12'!D$4:$D$50))
I did not put the [12] excel added that automatically I had 1:12
=SUMPRODUCT(--(THREED('1:12'!$A$4:$A$50)=A10)*(THREED('1:12'!$B$4:$B$50)=B10),(THREED('1:12'!C4:C50)))
I just seen the THREED for the first time today and am not sure if this was the correct place to try but it didn't work anyway
I have 3 sheets in the same workbook that I want to print in 1 PDF report, is there a way that I can do this?
View 2 Replies View RelatedI have an Excel file with 70+ tabs and was wondering if there was some way to group them together, short of just making separate worksheets.
View 6 Replies View RelatedI am looking for a macro or a formula that can give me multiple tabs, what i need is jan 01 to april 30,the next 2 books i could do by copying of course i have looked at the macros on here and no nothing about them ....
View 14 Replies View RelatedI would like this Macro to run on 6 different tabs in a worksheet as one Macro. It is currently only running on the active sheet. The tabs that I need it to run on are labeled "Totals" "New" "Used" "Service" "Parts" "Other Income-Ded"
Sub ExpenseAnalysis2012()
Dim rngSource As Range
Dim rngDestination As Range
Set rngSource = Range("D3:E90")
Set rngDestination = Cells(3, Columns.Count).End(xlToLeft).Offset(0, 2)
rngSource.Copy
rngDestination.PasteSpecial (xlPasteValues)
End Sub
How can I sumif over multiple tabs?? Without using sumif(a:a,sheet1!a1,c:c)+sumif(a:a,sheet2!a1,c:c)
Like sumif(sheet1:sheet2!a:a,a1,sheet1:sheet2!c:c) I do not want to use a macro. I have about 80 sheets.
Sheet1
Apples 93
Pears 49
Oranges 20
Bananas 35
Sheet2
Apples 100
Pears 13
Oranges 2
Bananas 350
How can I do
=AVERAGEIF(AZ6:AZ1721,">0",AZ6:AZ1721)
But across 18 tabs all with the same ranges???