Why won't this SUMPRODUCT work?
SUMPRODUCT(Sheet2!D1:D2000=OPEN,Sheet2!F1:F2000)
I have a range (D1:D2000) that contains a 'Status' value.
OPEN is a named range that refers to eight possible Status values: Assigned, New, Hold, Re-test, In Progress, Failed, Ready for Push, Coded
The formula should sum the values in F1:F2000 for any row containing one of the eight statuses in D1:D2000.
Im Trying to use a formula of countif or sumproduct
so the formula will read the range of cells from A2:A30 to see if the word Hydro is in any of those cells, then if the date range from Cell B2:B30 is from range =>Dec-01-2007 to =<Dec-31-2007, then it will count 1.
[Code] ........
But this formula doest count.
I've tried using a multiple if count if with arrays and sumproduct. I really want it to count the ammount of time the work hydro is used during the month on december .
I'm still using XL2003 (Heck, they JUST upgraded from 2002 last year! So at this rate, I'll probably get 2007 here in the office by 2012).
I have a few named ranges that use Indirect & Count functions to auto-adjust the range to the last row. Something like this.
Name: ProjectID
Range: Sheet1!$A:INDIRECT("$A$"&COUNT($A:$A))
When I try to include the named range in a sumproduct statement, it crashes XL the moment I type the evaluative sign (< = >) within the 2nd bracket. Like this:
=SUMPRODUCT(--(ProjectID>2500))
(The actual formula works on several other columns of data. I'm citing a simple example, which also crashes my XL.)
Is that my installation causing the error or something wacko in XL? Please try and write back.
The attached spreadsheet has a "master" workesheet in which I enter customer info, salesperson info, and date. The totals spreadsheet automatically calculates number of sales, contact value.
I need to modify the following formulas to only calculate the data within a date range shown in 2 cells.
=COUNTIF(Master!A1:A176,PayPeriod!A4)
=SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
=E4SUMPRODUCT(--(Master!A1:A176=Totals!A4),(Master!K1:K176))
I am trying to put a SUMIF formula at the bottom of the data. Doing it manually is fine, but I want some vba to automate it, and my vba doesnt work.
Sub balance()
'adds journal line so journal balances in each month
Dim Rga As Range
Dim Rgb As Range
Dim Rgc As Range
Dim Rgd As Range
Dim Rge As Range
Dim Rgf As Range
This code just inserts text into the formula, rather than the range of cells.
The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))
B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.
Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).
The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.
I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]
I am very comfortable with SUMIF, but need to search a range any any cell containing words or phrase "20%" then need to sum those corresponding cells only.
I.e., something like: SUMIF(A1:A10,..."20%",C1:C10) .... only sum all C cells where an A cell contains any combination of words with "20%" in them.
ColAColBColC
1 FA4,000.00 10%
2 CB1,500.00 15%
3 FA7,500.00 15%
4 AA700.00 5%
5 JG250.00 5%
6 FA1,200.00 5%
7 AA900.00 5%
8 CB175.00 15%
9 FA2,000.00 2%
At the foot of the spreadsheet I would like to show the total of the calculation
Col B * Col C, if Col A shows a value of "FA"
Having veiwed similar posts, I believe SUMPRODUCT may be the correct formula, but I cannot seem to achieve the result I am looking for.
So in the example the desired result is 1,625 B1 * C1 + B3 * C3 + B6 * C6 + B9 * C9
How can I write a conditional sum formula that will skip a criterion when it is left blank?
I was able to make this work:
=CHOOSE(COUNTA(B:B),SUMIFS(F1:F4,D1:D4,B1),SUMIFS(F1:F4,D1:D4,B1,E1:E4,B2))
where column B has the criteria.
I was wondering if there was a better way using Sumif or Sumproduct.
in colum A i have a list of dates, in colum B i have a list of amounts. what i need to get is the sum of the amounts where the dates in colum A are between the dates in cells C1 & D1. Tries sum producst but all I get is the total sum of colum B irrelevant of the date!
i would to combined these together
can this be done
I have 4 columns, A B C and D. They are a mix of formats.
A= Name (text)
B= Date
C= Code (text)
D= Number
I need a formula that gives me the total from column D, provided conditions for columns A, B and C are met. The date condition is month only, so I'm using MONTH(1) for January etc.
I can find a formula if there are only three columns total (using SUMPRODUCT), but not 4.
I have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name.
I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information:
=SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)
I'm trying to do here is if a layaway has been paid off then there will be a tax amount in column C. IF there is tax in column C and column A has the word layaway (or the word way) in it then I need to find out how much the total of the layaway $ amount was. Meaning, to figure out how much the tax is for.
For example:
If the text in Column A contains the word way AND column C contains a $ amount then return the amount that the tax would be for. Of course this would be an accumulated amount for all of column C. So, if Layaway is in column A and $3.13 amount is in column C then it will return $50.00 as the original selling price. Tax here is .0625 %.
a1=good
a2 = 100
b1 = bad
b2 = 50
c1 = good
c2 = 100
would like to sum a2+b2+c2 only if a1,b1,c1 = good.
I need combining sumif & sumproduct. I have attached a file which explains what I need.
View 2 Replies View RelatedFrom some fleet data i am trying to total any journey which took place between two times.
The time format is hh:mm:ss. I want to total the miles travelled in each trip between 5pm and 5am for certain vehicles
Vehicle Registration is in colum A, the times of the journey are column B and the miles traveled are column D.
=SUMPRODUCT(--(A2:A100=LG52RYT),--(B2:B1000=NOT Sure???, D2:D1000)
Would I use the above formular? And what would i put in the section i have marked "not sure"?
I need to convert the following formula to a Sumproduct so the link will not be broken when I close the linked workbook:
=SUMIF('Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!$E$231:$E$332,"M&R Parts D",'Z:WUTDaily Report[2010 Monthly Rpt - CPM - Thru November only.xls]2010 Expense'!AE231:AE332)
I need to add amounts in column B, based on column A’s account numbers, so I want to add only account numbers, say 17101 & 17201 and nothing else.
View 9 Replies View RelatedI have a daily tracking sheet. I want (off to the right) to be able to enter start/end dates and have it sum the total grossage for JUST those dates alone. Which function do I use?
http://s401.photobucket.com/albums/pp94/nmweir/?action=view¤t=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">
direct link? :
http://i401.photobucket.com/albums/p...r/untitled.jpg
I am using the SUMPRODUCT formula as a SUMIF with multiple conditions in the following format:
SUMPRODUCT(-(Range1=CellReference),-(Range2>0),Range3/Range2)
THis works with or without the 2nd condtion, but I thouht the "greater than 0" bit would eliminate the inclusion of 0's; however, the same #DIV/0 error occurs with or w/out that ">0" condtion as the 1st OR 2nd argument.
I speculated that b/c the final argument was dividing each paid of cells in either range instead of a typical sum, this caused the divisor error.
I am using the SUMIF formula to sum all the cells that meet a certain criteria and at the moment, this works fine. Now I want to extend the criteria to 2(3 later) conditions. I've read that the SUMIF function only handles 1 criteria. Is this correct? If so should I use SUMPRODUCT or DSUM? I eventually will have to check about 2000 cells in a workbook. Sample work book attached: where I need to total the price of all the items that are shoes and are red in colour. A pivot table beckons in the future, but for now I need this in a workbook format.
View 3 Replies View RelatedI am trying to extract data all countries excluding Spain and Portugal and also exclude data for certain product classes (for example excluding Products X, Y and Z)
I have written the formula below which picks up data for certain criteria I have concatenated in tab "raw data" in column A:
=SUMPRODUCT(SUMIF('Raw Data'!$A:$A,B7&D3,'Raw Data'!$W:$W)=('Raw Data'!I:I"Spain")*('Raw Data'!I:I"Portugal"))
Notes: Cells B7 and D3 are used match criteria in Raw Data A:A
Column W is the data that I need to extract i.e the numbers
Raw data I:I is a list of all countries
However this does not exclude Products X, Y and Z, only excludes countries Spain and Portugal.
I wondered if there was an easier way to create a formula?
-I have 3 relevant columns, A, B and C with 999 rows
-Column A is a status indicator "Yes" or "No" are the only options
-Coumn B a list of vendor names, which requires the wildcard example:"*Verizon*"
-Column C is the data range that needs to be summed
Right now i have this:
=SUMPRODUCT(--(A2:A999="No"),--(B2:B999="*VERIZON*"),C2:C999)
But, it seems sumproduct does not allow wildcard matches within it's "--" arrays. I am willing to try something else if possible, but I lack the excel knowledge to answer this myself.
Is there a way to replicate this code, so I don't have to Do the With [CODE] .......
View 9 Replies View RelatedI 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?
I have an array that contains order numbers, tracking numbers and shipment costs. I want to get the total value of the shipment cost per order. the problem is, there are some duplicate shipments (ie same tracking number) and I don't want to include those. I can't delete the duplicate entries from the database for reasons I won't go into here.
so I tired to use a formula like =SUMIFS(C:C,A:A,A1,B:B,B1)
A B C
11462046 CJ225083125US 10.51
11462051 CJ225082247US 17.04
11462046 CJ225083125US 10.51
11462046 CJ225083564US 22.40
the formula doesn't work (won't even let me enter it) but if it did, it should give a result of 32.91. it would add C1 and C4 (but not C3 because even though A3=A1, B# also equals B1 and that is what I don't want to add.
I think maybe a sumproduct formula is what is needed but the negative criteria is throwing me for a loop.
I want a sumIF function (based on three criteria) to fill the values of cells in a column so long as there are values in the cells in the column before that one. I was working with trying a loop, but have been shown a faster way is using the With function.
There are four named ranges that all exist on a separate sheet in the file: Crude, Location, Year and Volume. Based on the first three columns of the file I wish to have an output for the sum of volume based on crude, location and year.
Right now, my output simply returns the total sum of the "volume" range in each cell in the output column.
For example, if total sum of values in the volume column is 100 then my output column currently looks like:
100
100
100
100
etc...
Here is my code including the named ranges:
[Code] ......
See attached for an example. I'm trying to use the SumIF funtion with a named reference as the criteria. Here is my formula. =SUMIF(B10:F10,"<MinReq",B10:F10) It keeps returning 0 even though there is valid data.
View 3 Replies View RelatedI am using the SUMPRODUCT formula below and it seems to be working
=SUMPRODUCT(('Attendance Data'!H:H="XXX")*('Attendance Data'!D:D="YYY"))
This, as I understand it is counting the number of times a row contains the text "XXX" in column H and also contains the text "YYY" in column D.
This seems to work for me.
Now my problem is when I want to change the formula to search for the text "XXX" in column H and also any text that apperas in a named range on another sheet in column D. The named range is called "New_Starters"
I am using the below formula which is returning N/A's.
=SUMPRODUCT(('Attendance Data'!D:D=New_Starters)*('Attendance Data'!H:H="XXX"))