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 RepliesI'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,"
How could I add ">0" to the below formula array?
{=IFERROR(AVERAGE(IF(Reports!$A$1:$A$4000=A5,Reports!$H$1:$H$4000/86400)),"-")}
Reports!$A$1:$A4000 = column A lists multiple agent's names
A5 = a specific agent's name
Reports!$H$1:$H$4000 = column H contains a number which has been converted from time to a number
86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)
The average I'm looking for will give me an average of all times which are greater than zero.
I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.
[=AVERAGE(IF(($A$3:$A$275=$P6)*($B$2:$M$2=Q$4),$B$3:$M$275))]
I have attached a sample workbook that includes the broken formula.
Average Formula Error.xlsx
I need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx
I am creating a summary page to analyse my golf scores. I have come to a dead end when looking at the Par statistics. Does anyone fancy a challenge and try to fill in some of the blanks on the summary tab. I am trying to use array formulas based on multiple criteria. for instance under the Best Par 3 section, it would look at all the Par 3's for whichever golfer is selected and then return the lowest number.
View 4 Replies View RelatedI have a report for which I would like to set up a Quarterly summary section. There are 6 working teams listed in my table and I have created two cells with data validation to allow me to specify which team and for which quarter I would like to pull data. My intent is to have a formula in one cell which can extrapolate the quarterly team information based on the values in the data-validated cells.
My worksheet is set up so that:
The data table (tblD) contains metrics for all teams for the full yearTeam names appear in Column A of the table and the reporting month appears in Column BCell M10 has data validation so that the only choices are: Q1, Q2, Q3, or Q4Cells X1:X4 contain the quarterly values mentioned aboveCells Y1:Y4 contain array references, so that Cell X1 = Q1 and Cell Y1 = {"January", "February", "March"}Cell N10 contains a VLOOKUP which returns the appropriate array from Column Y based on value in Cell M10Cell L11 has data validation so that the only choices are the 6 team names
Now I was able to get quarterly information by manually typing the 3-month array into my formula like so:
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],{"January","February","March"}))
BUT! When I try to use a formula which points to the VLOOKUP cell (N10)--thus allowing me to make the display dynamic--the result is always 0.
Code:
=SUM(SUMIFS(tblD[TU5],tblD[Team],$L$11,tblD[Month],$N$10))
While inspecting the formula in the formula bar, I noticed that the values listed in the array appeared with double quotes (i.e. "{""January"",""February"",""March""}"). I tried every permutation of typing the array in the lookup cells I could think of, but none would allow my formula to properly calculate the total I needed.
{"January", "February", "March"}
{January, February, March}
January, February, March
Although I don't often utilize arrays in my formulas, I do employ them from time to time. I'm hoping there's some factor I might have overlooked which keeps my formula from working. is there an issue preventing array references stored in cells from being used in 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] ....
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"}))
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
I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
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?
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] .........
SUMIF and SUMIFS formula, where in I want to set criteria in the formula as greater than or less than or equal to value derived from another formula. I am inserting the following formula but excel is not allowing me to enter the formula.
=SUMIFS($C$22:$C$30,$E$22:$E$30,>=LEFT(L21,3),$E$22:$E$30,<=RIGHT(L21,3),$A$22:$A$30,G22)
I'm trying to sum over a range (column A) based on values in another range (column B). Column B's cell values are calculated with a simple numeric formula that is either a subtraction or an addition of two cell references. I want to sum all the column A cells for which the corresponding column B cells are determined by a subtraction. Then separately, sum the cells for which the corresponding column B cells hold an addition. It seems that SUMIFS is the function to use, but how do I specify the sum criteria as "a subtraction" or "an addition"?
Windows 7, Excel 2007
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
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].....
I've been trying to sum up invoices for specific contract lines while also taking into account if the invoice was deleted. There is a deleted column in which a "Y" is entered into the corresponding row meaning this invoice should no longer be accounted for.
I've been able to sum up the invoices per contract item just fine but I can't seem to get excel to account for the deleted column. I've attached a sample workbook which has dynamic named ranges already defined, the current array formula I'm using to sum up invoices and a pivot table that shows what the correct invoice amounts should be.
I am trying to do a formula that will add two different sums and so far I have this:
={SUM(IF('FAC Open POs'!$F:$F='Cash Waterfall'!$B6,'FAC Open POs'!$V:$X))}+SUMIFS('Cash Report'!$O:$O,'Cash Report'!$S:$S,'Cash Waterfall'!$B6,'Cash Report'!$T:$T,'Cash Waterfall'!G$4))
But I keep getting an error. I know that the formulas apart work, but I keep getting an error with trying to use an Array formula like this.
Attached file should be self-explanatory.
I want to be able to set sum range using lookup or match.. For example if I change B2 value to Nov-13, I want it to sum column K.
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
I have an array of data that i need to find multiple data points in: ...
View 11 Replies View Related=COUNTIF(A$2:T$1001,V2 & W2 & X2 & Y2)
I want to copy and paste this formula down column Z.
I want to count or add 1, only when a row of 20 cells (A to T) contains all 4 criteria in V W X Y.
I;'m trying to create an ARRAy formula to SUM with; the formula has to match 3 different criteria's. I'm trying this with no success, here is the last one I tried.
=sum(ifs(DATA!G:G,Sheet1!A27,DATA!B:B,if(Sheet1!B1,DATA!I:I,">=0")
with a multiple criteria index match array!
I have attached an example where I need to bring back a result matching 4 specific criteria, but I cannot seem to get it to work at all!
I have attached an example dataset with the formula that I was trying to get right (and failing miserably!!)
I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".
VB:
Sub test()
Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL")
Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT")
Set r = ws2.Range("A1:du4783")
[Code] .....
I have used INDEX/MATCH/ROW/SEARCH functions, in different permutations, but I am unable to get the result. The data set is something similar to the below:
Car
Region
Own
Use
Color
Honda
North
Yes
I use it to go for work
Green
[Code] .....
I want to be able to do following (2 separate tasks):
Task 1 (if in A1 on a new sheet, I had Use, i want to list all the items in an adjacent column, skipping the blank rows)):
Use
I use it to go for work
Family trips
Weekend fun
2nd car
Work
Task 2 (if i had Honda (A2) and North (B2), I want to have the colors listed in Column 3):
Car
Region
Color
Honda
North
Green
White
I know this can be done by an auto-filtering or manual sort, but I have work with thousands of similar data on a regular basis, and i want to find a formula that will allow me to list the items based on different criterion.
Need to expand the crteria on this array?
{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}
I need to add
{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}