Sumif If The Column Is In The Correct Period
Jan 24, 2007
I have a set of costs in column c (more that shown below) that have an indicator - for example 0 or 30 (there are more).
I need to populate rows 18 and 19 with the sumif - i have other tables where it is a straighforward sumif as the periods align, here though I need to check that the periods (rows 4 & 16) are checked and then the right data entered underneath.
I can't find a way to combine the sumif, with an if statement checking that the period is correct too? ...
View 9 Replies
ADVERTISEMENT
May 18, 2012
Users copy and paste source data from a report into worksheet 1 each month. Data from last month is deleted and data for the current month copied into worksheet 1.
I am trying to write a formula within worksheet 1 to check that data for the current reporting period only is in worksheet 1. For example all data from last month's reporting period has been removed and the only data in worksheet 1 is the current reporting period.
Reporting period is shown in two columns Year and Period number (1 to 12).
View 4 Replies
View Related
Apr 20, 2009
This question is about punctuation.
I know the following is a correct way to reference a question mark and a period....
View 4 Replies
View Related
Oct 7, 2008
I have a logbook which already uses a number of SUMIF formulas but have been trying to create one so that totals the number of hours flown in the above mentioned periods. I am attempting to copy the formula and then modify for the relevant column in this case: =SUM(IF(FlightDates<(TODAY()-90), 0, FlightLength))
FlightDates is the named range of the calender date in column A and FlightLength is the named range for total flying in column Z. However even though this forumla works in another situation e.g. tracking number of landings in the last 90 days, it comes up with an error.
View 3 Replies
View Related
May 24, 2012
I'm trying to add the total product sold by a group of sales person for example : Chad + Javier. My actual worksheet includes 600 rows of salespeople and 20 rows of product, i'm using this simple example to get the message across.
I was using =sum(sumif(A1:A9,"Chad',D1:D9),sumif(A1:A9,"Javier",D1:D9)), the formula should have given 91, but instead is calculating 113...
Salers PersonProduct AProduct BProduct C
Chad567
Chad567
Chad567
Chad567
[Code] .....
View 1 Replies
View Related
Apr 21, 2009
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
View 5 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Apr 28, 2009
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................
View 2 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Nov 3, 2008
I have a column with numbers ranging from -5 to 250. I want to write a formula to correct the numbers in the next column. If the number is less than 0, I want to replace it with 0,if the number is greater than 150 i want to replace it with 150 and if the number is in the 0-150 range it should be kept the same. Is there a way to use inequalities in a formula to do this??
View 2 Replies
View Related
Apr 14, 2014
I have a colum full of durations in 00:00:00 format. I need a forumla to look at these durations, count the seconds and then multiply those seconds by a predfined rate. In effect giving me a second colum full of costs.
I have attached a screenshot f the data, and the rate should be 0.0073p (.73 of a penny)
View 5 Replies
View Related
Jun 19, 2014
So yesterday I created a thread [URL] ..... that would clear a specific content if a date (or rather a day) matched the criteria.
Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.
Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.
So, I want to sum all the "x" for February 2014.
I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no success.
See attached file for example and for my =sumproduct formula.
Excelforumexample1.xlsx
View 5 Replies
View Related
Jun 29, 2014
I have started a user form called submit stock. What i am trying to work out is if i can use this form to enter the data to the worksheet using the form to specific columns depending on the first choice.
View 8 Replies
View Related
Dec 8, 2011
I want to create a Macro that uses IF statements to enter the CORRECT VALUE into COLUMN “Q” in the ACTIVE WORKSHEET. I am providing an example of what the data set looks like at the very bottom of this post. I want to use a Macro as oppose to Formula in the worksheet because I want to turn the Macro into an Excel Add-In.
I want the Macro to do the following THREE THINGS:
1. IF the Value in COLUMN L is “0” THEN enter “n/a” into COLUMN “Q”
2. IF the Value in COLUMN L is “2” THEN enter “n/a” into COLUMN “Q”
3. IF the Value in COLUMN L is “1” THEN use a formula that looks like this:
= O8 + ( ( O8 / P8 ) * (First SUM the HOURS in COLUMN O for all the ROWS that have the SAME VALUES in COLUMN C and H and a “2” in COLUMN L and then MULTIPLY that Result against those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours). The RESULT of the FORMULA would be entered into COLUMN “Q”. Once you look at the example at the bottom of this post it will definitely start making sense. I highlighted rows 8 – 11 for you in red in the data set at the bottom of this post just to center the attention on the rows I am talking about.
Here is an example of how the formula will work:
I will use ROW 8 from the data set below as an example. From the dataset I know CELL O8 equals 10 and CELL P8 equals 76. Now I will SUM the HOURS in COLUMN O ROWS 10 and 11 for ALL ROWS that have the SAME VALUES in COLUMN C and H in this case the values are “Times” and “PM” and contain a “2” in COLUMN L which happens to equal 3, then that 3 should only apply to those rows with a “1” in COLUMN L whose COLUMNS C and H values match up exactly with those of the Summed Hours. So now I plug that into the formula: =10+((10/76)*(3)) and MY RESULT which will go into COLUMN “Q” IS 10.395.
Here the DATA SET:
Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J Col K Col L Col M Col N Col O Col P Col Q Col R Col S Col T
Row 1 B ID Name Org Div T Number Model Make S Function E Function Type Description P ID OG Hours Sum EA P Hours Sum P S Hours EQ
This is what is happening in Column Q Explanation of Formula
Row 2
Times
Cont
2
28 28 n/a
n/a If formula populates Column Q cells with "n/a" if Column L cells contain a "0" or "2".
Row 3
Times
Cont
1
404 1194 413.4740369
O3+((O3/P3)*28) You get the Sum "28" by adding all the "P Hours" in Column O that match these 3 values:
1. The "Org" value of "Times" in Column C
2. The "S Function" value "Cont" in Column H
3. The "P ID" value "2" in Column L
[Code] ...........
View 9 Replies
View Related
Dec 16, 2012
I have an worksheet that refreshes every day from a .csv report. I would like to be able to have a comments column at the end that users type in. The problem is how to keep the comment next to the row it was on before the refresh.
When it refreshes there will be new rows, rows that are no longer there and all in a different order.
There is an Order Number column that can be used as an ID column.
View 3 Replies
View Related
Aug 20, 2008
B. start time C. end time D. Total time (military time)
Example: 2300 - 0300 = 4 hrs worked.
=IF(C19-B19>0,C19-B19,1+C19-B19)
I have a row for every date the hors is worked.
I cant seem to get the total hours to calculate correctly at the bottom of column D for a total hours worked in a particular pay period. It calculates a total up to 24hrs then reverts to zero. I have tried [h]:mm =SUM(XX:XX) and just about everything else I can think of and cant get it to calculate a correct total over 24 hrs.
View 5 Replies
View Related
Jan 11, 2013
I have "systems" that have multiple "units". I what to sum values for units that have EXACT MATCH in the system column. Example data is below.
I am trying things like =SUMIF(B:B,"EXACT MATCH",C:C)
How do I express exact match? For example the first 3 in the list I would want to return a value of 9. Note: Lists can have up to 3000 rows and > 1000 system ID's.
Unit ID
SYSTEM ID
Qty
MRK0012179
MRK0012179
3
[Code] ......
View 9 Replies
View Related
Mar 20, 2012
I have a worksheet (A) similar to the following:
Jan '12, Feb '12, Mar '12 --> Header Row
-5, 10, 2
6, -2, 3
5, -2, -1
I have another worksheet (B) with the same header Row (Jan '12, Feb '12, Mar '12). I need to create formula in cell A2 on worksheet B (right under Jan '12) that dynamically locates the Jan '12 column in worksheet A and then sums the numbers in that column only if they are < 0.
So in cell A2 on worksheet B (under Jan '12) I'd have -5 (only -5 is < 0), in cell B2 on worksheet B (under Feb '12) I'd have -4 (-2 + -2) and in cell C2 on worksheet B (under Mar '12) I'd have -1.
I've tried several variations of sumproduct, sumif, index, etc. with no luck.
View 3 Replies
View Related
Nov 13, 2009
I have Excel 2003 and my problem is to execute a SUMIF formula between 1 column (where a simple equal criterion must be verified) and 1 row.Let's suppose that column A contains these values:
A1: a
A2: b
A3: a
A4: d
and suppose that the criterion is the equality with the character "a".
Let's suppose that 6-th row contains these values:
A6: 1
B6: 2
C6: 3
D6: 4
Now, I would like to execute a SUMIF between the first 4 elements of A column (a, b, c, d) using as criterion the equality with "a", and the first 4 elements of 6-th row (1, 2, 3, 4).............
View 5 Replies
View Related
Apr 30, 2008
I am trying to use a sumif formula to sum an entire row if it meets the selection criteria
For example if I have months in Column A (appearing more than once), and Various Totals for each month in Columns B, C and D I want to be able to sum the total of B, C and D for every occurence of the month in my list. Trick is I want to be able to avoid sticking a total column in say Column E
I tried SUMIF(A:A,"Month",C:E) but it is only adding up column C
View 9 Replies
View Related
Jan 27, 2009
I have a spreadsheet that lists something like this (an easy version of it anyway!)
On tab 1 of my spreadsheet I have a table with lots of data similar to:
Column A Column B
111 12.23
111 14.32
222 48.23
222 28.34
222 10.23
222 33.40
333 23.21
On tab 2 of my spreadsheet I have unique versions of the data Column A on tab 1, for instance:
Column A
111
222
333
I would like to have a formula on tab 2 that can compare column A on tab 2 with column A on tab 1 - if it is similar I want it to total the amounts for all of the values equal.
So for instance, on tab 2 the final data should be this in my example:
Column A Column B
111 26.55
222 120.20
333 23.21
I've seen formulas that are similar.. but the condition is it can only total the rows that match. And preferably can do this from the other sheet.
View 2 Replies
View Related
Apr 22, 2014
In the B column i have dates. In the C column i have total hours.
Right now the following code counts how many instances of a date (Dt) are found and makes a decision if it is more than 3.
VB:
IVAL = Application.WorksheetFunction.CountIf(WS.Range("B:B"), Dt)
If IVAL > 3 Then
What i need is to SUM the hours (C column) for each instance of the date (Dt) in the B column, and test if it is greater than 24. so it would be similar to this:
VB:
IVAL = Application.WorksheetFunction.SumIf(WS.Range("C:C"), Dt)
If IVAL > 24 Then
Except that this code attempts to sum the hours of the C column and also wants to recognize the dates from the C column which would not work. I need something different than SUMIF. I need it to Sum the C column for the appropriate dates (Dt) in the B column.
View 2 Replies
View Related
Feb 25, 2009
I am try do is a sumif that Excludes a column if it as 100% in there, I have managed to do the sumif (( =SUMIF(A:A,F3,E:E)+SUMIF(A:A,F3,F:F) )) but
I dont know what to put if I want to ignore it, if it has 100% in a cell
I have attached a test sheet if someone could have a look and try point me in the right direction
It works out a percentage returned if column E / F = the full amount in column k
View 5 Replies
View Related
Jun 14, 2013
I have a spreadsheet using a SUMIF formula =SUMIF(G:G,A2,D:D) that is working fine.
However, I wish to introduce a "multiplier" column (E). That is, if the corresponding row has a figure in the E column I wish to multiply that figure with the corresponding figure in D.
D E G
6 5 A
5 0 B
4 0 A
A = 6 x 5 + 4 = 34
B = 5
View 3 Replies
View Related
Jun 18, 2009
How would I SUM all the results from results 1-5 if say ITEM in column A = C?
SUMIF wont work as it only sums the first column...
ABCDEF1ITEMRESULT 1RESULT 2RESULT 3RESULT 4RESULT 52A1227723B1075554C21772 5A1010 3376A5 23357B133 7 8C7 1 29C555 10B72772
View 8 Replies
View Related
Aug 29, 2013
How to use formula approach to get the total figure for A & B
A
1
2
3
B
4
5
6
[Code]....
View 9 Replies
View Related
May 1, 2006
I'm trying to use the Sumif formula when the "Sum_range" parameter is dynamic. I can identify the column number (by using "match" formula) but I have no idea how to use this information as a parameter in my SUMIF.
View 2 Replies
View Related
Sep 17, 2006
I am trying to setup formula so I can enter column letter and sumif should update with proper sum range to be summed ...
View 4 Replies
View Related
Jul 17, 2009
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
View 9 Replies
View Related
