SUMIF With Two Text Based Criteria Using INDIRECT
Apr 23, 2008
I have a need to sum values in the right column of a 2 column table based on multiple criteria of the values in the left hand column. The table has 500 rows.
The two criteria are:
1 - that the cell contains a specific text string ("product a")and
2 - that the cell contains a text string for the month in question ("Jan")
The other limitation I have to work with is that the formula is in a different workbook to the source data. I have a worksheet that lists the external workbook/sheet and have been using INDIRECT in the DSUM.
I read through the examples here:
[url]
Basically they recommended DSUM as the most efficient.
I can get the DSUM working, but it needs the workbook to be open before it can evaluate the expression and therefore I get an error when I select to update values upon opening the workbook.
So in summary I wondering if anyone can show me how to write a SUMIF formula based on the above criteria.
View 9 Replies
ADVERTISEMENT
Aug 11, 2013
Excel - 2010
I have the following formula:
=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")
TRANS_Cost = B:B on FEB tab
TRANS_Cat1 = C:C on FEB tab
TRANS_Cat2 = D:D on FEB tab
CAT_Main3 = "Everyday_Expenses"
TRANS_Status = E:E
In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.
View 2 Replies
View Related
Aug 8, 2006
I want a formula to sum all values if two different criteria match. I've attached a sample below. The TaskID and the Time Entry ID are the two data points I'm searching for and the totals column is where my sum statement would be. The numbers are being pulled from another worksheet.
View 7 Replies
View Related
Jul 22, 2002
The following table is used in the sumif.
I would like the sumif to use the range with the names, and the range with the dates.
I have a GUI in which the user enters a name and a date and the $ amount earned for that week should appear.
The criteria is the name and the date.
******** ******************** ************************************************************************>
Microsoft Excel - Business Book.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutP20=
IJKLMN3******4michaelb1*14$400.007/21/20025Michaelb1215$450.007/21/20026
[Code] .........
View 9 Replies
View Related
Apr 18, 2014
A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3
How to sumif all 3 criteria are met?
View 7 Replies
View Related
Oct 21, 2008
I'm trying to get a simple Sumif formula to work. I have used this formula a hundred times before but this time the results are inaccurate. I suspect that it may be because of the math symbols which are used in the criteria text i.e. < and >
My formula is as follows: =SUMIF('Raw Data Export'!D:D,"< No Project >",'Raw Data Export'!G:G)
A data extract from the sheet named "Raw Data Export" is attached.
View 6 Replies
View Related
Aug 22, 2011
I have a table with
Column A - Suppliers
Columns B to M products with a product group (in row 1)
Prod Group..Core.......Core........Outer........Inner.........Core
Supplier......Type A......Type B.......Type A........Type B.......Type C
AB Ltd........1000.........2000..........500.............750...........5000
CD Ltd........3000.........5000..........100.............950...........8000
AB Ltd........2000.........4000..........600..............800..........7000
I would like to know how to sumif when for eg supplier is "AB Ltd" and the product type is "Core" in this eg = 21,000 (how to paste a table)
View 9 Replies
View Related
Sep 19, 2012
I have different products with different sizes becoming operational in various months & years. I need to sum those sizes that are operational in a given month/year. In the example below, I have
-product A with 100 unit size, operative since 2007/03
-product C with 200 unit size, operative since 2007/10
So in the table with months on left, and years on top,
from 2007/01 to 2007/02, value must be 0,
from 2007/03 to 2007/09, value must be 100,
from 2007/10 to 2008/06, value must be 100+200=300 and so on.
For this specific example, I tried the following:
Code:
E2 = D2&TEXT(C2,"00")
fill to E7
B11 = sumif(E2:E7,"
View 2 Replies
View Related
Oct 15, 2013
I am trying to use the sumif and indirect function together but I keep getting an error and I'm not sure why.
Actual formula:
=SUMIF('SWG OR'!B:B,A168,'SWG OR'!E:E)
Using the sumif/indirect function, I have the following:
> Cell B5 contains the name of the tab "SWG OR"
=sumif(indirect(B5&"!B:B",A168,B5&"!E:E"))
View 2 Replies
View Related
Jul 12, 2006
I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.
1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.
2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.
3) Is it possible to use a vlookup function for this solution?
View 9 Replies
View Related
Dec 9, 2013
I am trying to stream line the recording of time tickets . I would like to create a Summary sheet that shows the amount of hours each worker has done based on that particular day. The trick is I want the summary sheet to be automatically updated when the each individual time ticket sheet is completed. I would also like to be able to drag the formula across a row so I do not have to recreate the equation each time a new time ticket sheet is completed. Each time ticket sheet is its own sheet and is identified by date. Each time ticket ticket sheet is identical and is already created to so all you have to do is click on that the sheet reflecting the date and fill in the information.
This is the equation I have created but keep getting #VALUE!
=SUMIFS((INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$L$11:$L$34")),(INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$B$6")),('Data (2)'!J$8),(INDIRECT("'"&MONTH(J$8)&"-"&DAY(J$8)&"-"&YEAR(J$8)-2000&"'!$C$11:$C$34")),('Data (2)'!$I$9))
The sum_range is Manhours from L11:L34 for each time ticket sheet. The criteria_range1 is date in cell B6 listed on each time ticket sheet, criteria1 is date listed on cell J8 of the summary sheet . The criteria_range2 is names from C11:C34 for each time ticket sheet, criteria2 is name of person on cell I9 of the summary sheet.
Each sheet is labeled as 8-19-13, 8-20-13, 8-21-13, and so on. Summary sheet is labeled as Summary
The table that I am using to record the data on the summary sheet has the date increasing by one day on row 8. The names are vertically listed on column I.
View 1 Replies
View Related
Feb 16, 2010
How can I do SUMIFs based on columns that contain text? Im having some trouble with them.
I just need to check column H for the text "Newham" and then total the corresponding cells in column F
View 9 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
Nov 25, 2013
I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
View 6 Replies
View Related
Jul 19, 2014
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
View 1 Replies
View Related
Mar 18, 2009
Trying to sum a range based on the criteria of the 1st position within each cell in the range. Cells have data like "A-8", "A-4", for a total of 12 for "A". This is the formula I have that is not working.
=SUMIF(left(C4:P4,1),"A",RIGHT(C4:P4,1))
View 6 Replies
View Related
May 29, 2007
to run a macro or any other methods to repeat the same head rows in my database as followings:
Let's say the main head row is in row 1.
Item names such as Item A; Item B; Item C ....in column A
What I want is to insert the same head row after the list of each item.
Item name (head row)
Book A
Book A
Book A
Item name (head row to be repeated)
Book B
Book B
Item name (head row to be repeated)
Book C
Book C
View 9 Replies
View Related
Jun 2, 2009
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies
View Related
Jan 26, 2010
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.
Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View 2 Replies
View Related
Sep 4, 2007
I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.
I tried the following:
=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))
Column E would contain a date, such as 01-07-07 or 1st July 2007.
In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?
View 3 Replies
View Related
Sep 27, 2007
I tried to modify a sumproduct formula given previously by Kris which is to sum base on strings, to solve another problem but with no luck. My attempt without success is in BLUE column whereas the correct answers should be that in the RED column. Basically my problem is that if a condition is true, I want to concatenate the values of 4 cells with a space in between them.
View 4 Replies
View Related
Aug 24, 2009
I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
View 8 Replies
View Related
Aug 17, 2006
Need a way to count the number of times a certain bit of text occurs in a range based on a given criteria? Say I've got a column of names and another column with true or false in it (next to each name), how could a do a total of how many times 'TRUE' occurs next to each name?
View 3 Replies
View Related
Oct 17, 2007
I've attatched the workbook here with some dummy values in place of financial figures (We don't base our profit on RAND() formula's )
Essentially, I would like to search for CAN in A and SER in B and if they're found, to alter them to SYD (A) and CAN (2). It's essentially an automatic find and replace, but based on 2 criterias rather than one.
View 9 Replies
View Related
Mar 1, 2014
I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.
To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.
However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.
View 10 Replies
View Related
Jun 27, 2012
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
View 6 Replies
View Related
May 12, 2006
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
View 3 Replies
View Related
Apr 8, 2013
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
View 1 Replies
View Related
Feb 6, 2009
I am trying to take a census by date and name for every half-hour using the attached data. So for example, I would like to know how many people Roger had that walked-in and walked-out for each date on the spreadsheet and during what hours. The census would be every half-hour and not double count times. I could actually deal with just the min and max time for the day (by date and name) and having some indicator (say a one or an X) in the cells when Roger had at least someone anytime between walk-in and walk-out. It's kind of like getting a "time worked" based on the data that's provided to me. So I know how many hours Roger worked by day.
View 4 Replies
View Related
