I am trying to combine a If(is(numbersearch combination with a Frequency formula , i am not using sumproduct as want to return a complete total if criteria is blank.
When a certain validation is entered it returns the number of individual number combinations in a column ignoring duplicates (columns are number only not text)
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
Any way to cycle through two ranges and fill in blank cells. The worksheet has a "header"(A2:H2) that bascially determines how the rest of the sheet will be filled out. This header is subject to change if one of the headers is changes(see PLAN, and TRAN CODE in example).
PLANSSNDATEFUNDSRCTRAN CODEITEM CODEAMOUNT (CASH)56016999-99-9999FBLNK01699161.00022.00033.0060988999-99-9999fBLNK0150595.00026.00037.00
The SRC and AMOUNT(CASH) fields will always be complete. What I'm trying to do is cycle through AMOUNT(CASH) and if the field is not blank to then cycle through crng lookig for blank cells. If the cell is blank it should copy the cell above. This is where I'm headed but it will cycle through the same crng(A3:H3) every time.
Public sub Line_Check
Dim ccell As Excel.Range
Dim crng As Excel.Range
Dim hrng As Excel.Range
Dim hcell As Excel.Range
'skip A1, A2(add code to validate headers)
[Code] ........
I am creating a tracking spreadsheet where i can toggle between months (attached). It returns data properly when I select January or February from the yellow highlighted dropdown list. However, when i choose March, it returns 1/0/1900 because no data has been entered for March (table array) on the AAAG tab. How do I write the formula to return a blank cell on the summary sheet when no information has been entered into the table array yet?
View 2 Replies View Relatedin one column of my spreadsheet and it is returning the correct concatenation for the appropriate lines. However, I wish to have the column's cells return an answer of blank (" ") if B15, B16... etc is blank. In other words, at the moment, a correct answer would read something like '1810sd0000' C10 and B15 used, while an incorrect answer would still display '1810', but I want it to be a blank cell.
View 8 Replies View RelatedI am trying to get grades based on raw scores in (I15:AX15) and (i11:ax11) are percentages. If a cell is blank in the (i15:ax15) I don't want it to be used in the calculations.
=(if(sum(i15:ax15),round(sumproduct($i$11:$ax$11,i15:ax15),2),""))
I need to find the total number of rows down to the next blank cell (and then perform a function based on that number).
I'm using:
CountA(A1,xlDown)
Situation: I have a raw data import - each record is anywhere from 2 to 9 rows, and I need to move each row in that group into a column.
I would like to use something like:
totalRows = Application.WorksheetFunctions.CountA(Range("A1, xlDown"))
If totalRows = 4 Then
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Cut
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveSheet.Paste
etc.
May seem straightforward, but not to me. I need some code to scroll down column A and insert the sum total of A2:A16 in B17, and repeat this everytime a blank cell appears in colum B, continuing down to the end of the worksheet.
View 5 Replies View Relatedi need a formula that will return the total value of units which is greater than 20000 in col b between 5 and 30 days
AgeUnits272,472,215.29134,354.83112,714,300.007-112,566.006-239,853.006-105,938.006-36,792.006-608,695.001827,632.007-21.00
I have a spreasheet with partnumbers listed in column A, a safety stock value in colum B, a restock date in column c, and quantities listed by month needed in the following columns, i.e.:
A B C D E F G
Part # Safety Stock Restock Jan Feb Mar Apr
123abc 50 Feb 20 35 20 10
In the example above, it is apparent that the safety stock will be delpeted in Feb.
I am trying figure out how to calculate the month that the safety stock will be depleted
and show this in another cell.
I've got a spreadsheet with headers in column A and in rows 9 and 10. The headers in column A start in row 11 and are sizes (i.e XS, S, M, etc..). The headers in row 9 start in column B and are names (ie. First, Second, Third). The headers in Row 10 can be either Net Sales or On Hand Units. There are then values starting in B11 to L19.
The headers in column A do not repeat but the headers in row's 9 and 10 can repeat. What I need to do is create a function for a report page that will total all of the values that match to a particular header in column A, the header in row 9 and the header in row 10. So for example I would want to get the total for any instance when the header in column A is M, the header for row 9 is Second, the header for row 9 is Net Sales.
I've attached a sample of what my data dump would look like as well as the separate reporting worksheet where I want the totals to appear.
I am trying to create a summary sheet in excel and i am in need of a formula which gives me a summary in Sheet 2.
i.e. Match column A2:A19 with F2:F19 and give me the total in Sheet 02 with date and reason.
Sheet 01
In no
In Date
In Sector
STA
ATA
Out
OutDate
Out Sector
STD
ATD
Total
Reason
[Code] .......
Expected result in Sheet 02
Flt No
Date
Reason
AB318
AB300
AB030
AB420
AB484
[Code] .....
I have a pivot table which I am interested in filtering in several different ways. The table contains a category for AREA on the Y axis and BLOCKS on the X axis. When unfiltered the tables displays AREA 1,2, and 3 and has a full list of parts. I can then filter the table by AREA to look at one area specifically, and when I do the BLOCKS list shrinks to only those which correspond to that specific AREA.
My problem is that I want to be able to reference on another sheet the value that corresponds to AREA 1 Total regardless of whether the table is filtered to only AREA 1 or if it is unfiltered. I cannot just reference the cell because AREA 1 Total is in a different cell when unfiltered then when filtered. Is there any way to do this?
Is there some code that will return the 'Grand Total' figure from a set of data that has had 'Subtotals' applied to it?
Sheet1
BCDEFGHIJ2Product10/08/200917/08/200924/08/200931/08/200907/09/200914/09/200921/09/200928/09/20093A228157989393994B1784371107922385C4483398261701298
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
And I am after a formula that will return the total sum of values between two specific dates.
So if my results table looks like the one below, the values the formula would return are shown in Cells E9:E11.
Sheet1
BCDE8ProductStart DateEnd DateTotal9A24/08/200914/09/200934110B10/08/200907/09/200918511C31/08/200921/09/2009225
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
I have a spreadsheet with one "master" sheet and multiple "level" sheets. The workbook is an employee rating system and employees have different pay grades. The individual sheets represent each paygrade while the master sheet averages the total of each criteria for all the paygrades. Look at the image below:
http://www.excelforum.com//attachmen...1&d=1165960332
What you see above is a cutout from one of the "level" sheets to give you an idea of how employees are added to the sheets.
Sheets I have: Master/E1/E2/E3/E4/E5/E6/
The first catch is that the sheets will not have an equal number of "rated employees", so I would need to tell the program how to find the number of employees being rated (and how to reference the columns to find the total scores in). In the example you see name1 and name2... this represents 2 employees. To add another employee they duplicate the 3 rows and create another employee (i.e. name3). Second is that it has to disregard total scores that are "zeroes", meaning it does not average the total scores with a zero.
The master sheet is just a duplicate of the "level sheets" but with 1 set of 3 row columns to average each criteria from the 6 "level" sheets. I appreciate your guys' help in advance. Thanks!
- Kareem
P.S. Those that are wondering, Total score = Weight(blue column) * points (1-10)
I would like to know if it is possible to have a cell calculate a running total based on previous data as soon as you enter new information. The spreadsheet is a log of suspensions. Relevant columns include the student's name, date, and length of suspension (in days). For example, if Column A= Name (could be separated into first and last name if necessary, or merged into one column), B= Date, C=# of Days, D= Reason and so on, can Excel add the number of total days of suspension for all violations as soon as a new record is created? (If a student has already been suspended for one day, then gets suspended a month later for something else for three days, can excel tell you immediately how many days that student has been suspended total?)
This is actually a question from my mom, who asked me to help her, and who told me that a former coworker's wife had created a spreadsheet that could do this. Unfortunately, that spreadsheet no longer exists and they must recreate something.
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedUsing one spreadsheet with three date columns and two columns counting days.
If there is a value in Resolution date, then Column N is blank
If there is no value in Submit date and Resolution date, then Column N is blank
If there is no value in Resolution date, and there is a value in Submit date, subtract Submit date from todays date to show how many days it has been pending approval
Created on = J4
Submitted on = K4
Resolution Date = L4
Days to Approve = M4 I've got that formula =IF(L4="","",L4-K4)
Days Pending Approval= N4 (cell with formula)
If Resoultion Date L4 has a value, return blank
If Submit on K4 and Resoultion date L4 are blank, return blank
If Resolution date L4 is blank, and Submit on K4 has a value, subtract Todays date from Submit on K4 to show Days Pending Approval
Cell J4______Cell K4______Cell L4__________Cell M4__________Cell N4
Created on___Submit On___Resolution Date___Days to Approve___Days Pending Approval
4/5/13_______blank______Blank____________Blank___________Blank
4/5/13_______4/5/13_____4/7/13___________2_______________Blank
4/5/13_______4/5/13_____Blank____________Blank___________()Today-K4
Have a sample file attached : sample2.xls
What I want to do is - Column H shows my current position, and column L shows the p&l.
I would like excel to calculate the p&l for each position, and put the results in column M.
For example: H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit.
Have a sample file attached, and what I want to do is as follows-
Column H shows my current position, and column L shows the p&l.
I would like excel to calculate the p&l for each position, and put the results in column M.
For example,
H3 is the opening of a new position, as H2 shows the current position as zero, and H4 is not equal to zero. The position was added to in H4, and I'm now holding 2. The entire position was sold in H5, and my current position is back to zero. I would like excel to then show in M5, the sum of H3,H4 and H5, and then start the calculation again, and keep doing so down the entire column. If there was only 3 p&l cells for each position, it wouldn't have been a problem for me to get it done, but unfortunately, they can vary quite a bit, and I have no idea how to do it
I've inherited a reporting process which I need to convert to Excel but my knowledge of excel functions is fairly limited. I've attached a sample worksheet which sets out exactly what I'm trying to achieve in the comments. However to summarize here I need to add the data which meets set criteria together and display the result on a different worksheet. The problem I'm having is that the "criteria" the data needs to meet is dependent on info from other cells.
For example I only the totals of the data in column D where the date in column A is 01/01/08 and the data in column E is 1 and the result is to be displayed on a separate worksheet if that makes sense. It's difficult to describe without seeing the example workbook.
I have what I thought was going to be a simple function to create and, after a week of getting more error messages than I've had in a year, I'm ready to throw in the towel. Situation: There are two worksheets in the same workbook [Excel 2003]. One is for "Posting" details about technicians and their work orders. The other is a "Report" that summarizes the number of workorders open, closed, etc.
In the "Posting" sheet, I have created dynamic name ranges for two columns: Technician (4-digit text field) and InDate (date field that, if blank, means that the technician has not closed this work order. My ranges are set up like this:
Technician=OFFSET(Posting!$B$2,0,0,COUNT(Posting!$B:$B),1)
InDate=OFFSET(Posting!$D$2,0,0,COUNT(Posting!$D:$D),1)
So all I need to do is to total all the blank InDates for each Technician by employee number. I've tried COUNT to SUMPRODUCT. A co-worker got it to work with using...=SUMPRODUCT((ISBLANK(Posting!$D$2:$D$65307))*(Posting!$B$2:$B$65307=4288))
His solution was to list almost every available cell in each column. I just can't help but think that the dynamic name range should be able to work but I can't get it right. I think I'll choke if I get one more "N/A" or "VALUE! error.
I do regular saving on random date and un fixed amount as example below...My current investment amount on date 13/06/2013 is USD 20,000 .How much is total return per annum?
Date
Amount Invested
1/1/2005
USD 100
5/1/2006
USD 200
[Code] ...........
I use sumproduct to put total qty into the Defect Table. But it take a long time for excel to run the counting process. Problem: From master data sheet, I want to plot the qty into the defect table follow by date occurring and by section
View 2 Replies View RelatedI am trying to add separate columns together to get a total count of a specific criteria. The formula I am using is:
View 3 Replies View RelatedI use Windows Vista and Excel 2007.
So to cut to the chase :
On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.
Group 1: Cell H5, Cell I5, Cell J5, Cell K5
Group 2: Cell L5, Cell M5, Cell N5, Cell O5
Group 3: Cell P5, Cell Q5, Cell R5, Cell S5
Group 4: Cell T5, Cell U5, Cell V5, Cell W5
Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5
Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5
Then I have two columns following those: Col AG, Col AI
In each one of those two columns, I am trying to sum up a total value using The IF Function.
In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5
ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.
Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :
=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.
But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.
This is the way it looks in the spreadsheet:
Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/
Symbol (for P) Text Text Value /Symbol (for L) Text Text Value
And so on 4 more times across.
I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.
Clolumn D will always have a positive value, Column N sometimes will be zero.
using the formula =SUM(N6-D6) returns a negative value if 'N' is zero. I would like to return a blank cell or '0' if 'N' is zero.
if cell a24 has nothing in it how can i make sure a77 is blank at the moment it returns a zero
View 2 Replies View Related=IF(Q20+R20+S20>0,Q20+R20+S20,"")
V20
=SUM(T20*O20)
V20 gives me #VALUE
How can I have V20 blank if T21 is blank?