I'm trying to use the SUMIF function based on a specific month. This month formula has to choose from a range of dates. Currently, the month to report (5) is the only one that works. If I have dates in June and choose 6, it doesn't work.
This is the formula I'm using right now:
=IF(MONTH('Drafting Errors'!C:C)='Error Metrics'!C1,SUMIF('Drafting Errors'!A:A,C5,'Drafting Errors'!N:N),"No data for this month")
In attached sheet, I am trying to find total cost by month only for year 2009. Currently formula I have in Cell c24, is {=SUM(IF(MONTH(B2:B9)=1,D2:D9,0))} But this calculates for all years, not just 2009. How do I modify above formula, so for each month, it shows total cost but only for 2009?
View 2 Replies View RelatedColumn A have random dates and column B have numbers. How can sum numbers for each month By sumif function ?
View 8 Replies View RelatedI play poker, and I have a live session log in an excel spreadsheet, like so:
View 2 Replies View Relatedi thought i had something sorted out for this yesterday, but coming back to it today it hasn't worked at all.
i've created a spreadsheet with records of what jobs my heavy plant repair company has opened, which contains such data as when the job was if and when the job was closed and the value of the job when it was closed.
now i have to create a summary sheet that totals the values of the closed jobs in a given month. i've included an mock up of the database in question, but i just can't seem to get it to work.
How do I amend this formula to have it only trigger on a certain month.
=SUMIF(C:C,1,F:F)
Date Description CatCost feesTotal Balance
20/02/12 PayPal Credit 10$0.10 $0.00$0.10 $0.10
20/02/12 PayPal Credit 10$0.01 $0.00$0.01 $0.11
26/02/12 Payment 1$174.69 $4.49$170.20 $170.31
I need it to look at what month it is and then the category.
Column A have random dates and column B have numbers. How can sum numbers for each month By sumif function?
View 6 Replies View RelatedI have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View 4 Replies View RelatedI would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
I'm trying to write a statement to incorporate 1 more piece of the puzzle. the formula I have is =SUM(SUMIF(Sheet3!$F:$F,{"F12","F24","F26"},Sheet3!K:K)). In Column F, I have text and Column K, I have numbers. I want to add to the formula if value in K is less than 1, don't add it
View 9 Replies View RelatedI m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
I'm attempting to sum figures based on the current date. I have a month that's returned from a today function lookup in column B2 (currently 'October').
In F17:Q18, I have the following:
F
April
G
May
H
June
I
July
J
August
[Code] .....
I need to be able to look up today's month (in B2), and sum the row up to that point (so for October, sum Q18:L18). When it ticks into the next month, I want the sum to automatically update to sum to column M
I'm struggling with a SUMIF, my current one sums everything in the row and ignores my "<=B2"
I also tried (MONTH(TODAY()) with corresponding month numbers (Jan 1, Feb 2 etc) in Row 19, but that breaks because Jan-March are 1-3, and it sums them too.
As you can see it's built on a financial year. How to sum the rows up to the current month, and include last year once we're into January-March?
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
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 RelatedAs you can see I am using the code below in ( I ) =IF(OR(G5="",H5=""),"",-INT(-(-INT(-2*G5/C5)*C5-H5)/C5)*C5)
What I am trying to do is keep stocks, based on 2 x the sales, as you can see G5 I have 15 I still have stock of 35 so I should not need any stock but it has put 50 in. The 50 is a layer rate that I need to order in if I need any. If I had 20 sales and 19 in stock I would want it to order 50. It is the same for all the ones listed in the sheet apart from I8 where it should have ordered but only 40.
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] .........
I am trying to write a Sumif formula based on two conditions. I want to sum the data in column AK if the data in column D = BUD12 and the data in column E = US Custom. I have written the following formula but am getting a #VALUE error.
=SUM(IF($D$25:$D$700="BUD12",IF($E$25:$E$700="US Custom",$AK$25:$AK$700,0),0))
I have situation where my account (from trail balance) will present to different grouping based on reporting, example below. How I can formulaize or using VBA to sum to amount based on the format.
Example :
if i want the format b : if i sumif HCM - the total should be from account code a1001,a1002,a1003 and hrd will sum up based from account code a1004,a1005,a1006
Account Code
format a
Format b
format c
format d
format e
a1001
adm
hcm
adm
adm
hrd
[Code] ........
A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3
How to sumif all 3 criteria are met?
Have had a search around the web forums for an answer to this and am not getting anywhere.
In column A i have text - apr, may, jun etc
In column E i have general format text - a 6 figure number
in column M i have text - eg 886.97 (a currency, but in text format)
i want to:
where column a=apr and column E=212130 sum the contents in column M
I have a list of sales made during a certain period. They are either in £ or $ and are in a list. Essentially like this (but with lots more info):
sale1 £300
sale2 $450
sale3 £150
etc...
What I would like to do is to have two cells at the top which sum only the £ values and a cell whcih sums only the $ values. Is this possible?
My idea to was do a sumif formula based on the cell format of the sales value, but I can't see a way of doing this. The only other way I can see is to have a simple addition formula selecting only the certain cells i want, but this would be labour intensive to maintain, as the formula would need to be updated eachtime a new line is added.
I would like to create a UDF similar to the SUMIF function but the UDF would be dynamic in that it would sum based on a dynamic range that would change based on the row the user is in.
background: I have a very large input template for 12-18 months for multiple cost categories. I would like to have the UDF in a specific cell above each category that would show the user what the sum of the range in the current row they are inputing data. Each row represents a specific project/task (along with 12-15 descriptive columns) that makes the freeze pane option unusable.
I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A.
Here is the sheet that the information will be drawn from:
Part#QTYREP
4011A
4023A
4031B
401 1 A
Here is the sheet that the information will be output:
Rep
Part#AB
4012
4023
4031
Would like to know the formula to sum up values based on a range of numbers.
For example, to sum value in Column B if the range in Column A is within 8100000 to 8150000.
Column A Column B
8110000 110.00
8130000 250.00
9100000 300.00
I have just installed Excel 2007. Is there a way to sum a range of cells based on cell color in Excel 2007 without creating a macro or UDF?
I was told there was, but I can't find it and for some my reason my help index is corrupt.
I have attached an example. I am wondering how I can get [K2] to return the value 501 based on the lookup.
View 7 Replies View RelatedI 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)
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,"
B
C
D
E
F
G
H
I
J
K
L
M
N
2
Rep Names
9/1/2013
10/1/2014
11/1/2014
[Code] ......
Above Sheet name = Monthly Modified Targets
Below sheet name = Sales Stats Comparison
D
E
2
Date Format: MM/DD/YYYY
3
3/1/2014
5/31/2014
What I am attempting to do is to sum up the rows of the reps names based on the dates within the range.
So in this example above
Name1 = 0
Name2 = 55
Name3 = 25
Name4 = 0
when you change the date range it would change the totals.
The results of the example will be placed in
"Sales Stats Comparison" Range K11:K67
I have tried a few different options using index / sumifs / Match (date)
I just can't seem to pull more than one result and it does not sum up all the values in the row based on the date range.
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.