Sumif Trouble
Apr 20, 2009why the attached formulas ("Datasum" tab E2:AB4) are not working properly? I am not looking for a cumulative total (which I appear to be getting) but rather just the number of fruit requests by hour.
View 4 Replieswhy the attached formulas ("Datasum" tab E2:AB4) are not working properly? I am not looking for a cumulative total (which I appear to be getting) but rather just the number of fruit requests by hour.
View 4 RepliesI am a Project Manager, Surveyor involved in new ships building. I have to control and report the budget and actual hours every month and at the end of the project. I need to calculate the sum of the actually spent hours for the inspections done every month and send them to our head office. I use SUMPRODUCT function but Excel displays #VALUE! error eventhough there are no cells used in the formula which contain text!
The formula in E11 = SUMPRODUCT(--(MONTH(E2:E8)=MONTH(E10)),--(YEAR(E2:E8)=YEAR(E10)),H2:H8). E2:E8 and E10 are formated as a date. I tested all of them with ISNUMBER and the result is TRUE!!! Is there other formula I can use for the intended purpose?..............
I'm getting a compile error before I even get a chance to use it. I'm sure it's simple but it's got me stumped.
View 3 Replies View RelatedI'm having real trouble selecting a series of rows based on variables for the purpose of cutting them and pasting them elsewhere in the spreadsheet. For example, if this is my spreadsheet
row
2 I Hate
3 Bill
4 O'Reilly
I want my code to select the range of 2:4, cut them, and paste them elsewhere but I want 2:4 to be variables. For example
Fun = 2
Stuff = 4
Rows(Fun:Stuff).Select
Selection.Cut
But doing "Rows(Fun:Stuff).Select" does not work at all. If you do Rows(Fun).Select, this works to select Row 2, but I can not use the variables to select a range.
I have looked all over the place but nothing seems to work for me.
In detail, I have a quote book i made with two sheets for different types of items on them. For example:
Sheet 1 is for wooden items, and sheet 2 is for metal items.
So after i enter all the items on sheet one and sheet two, I want to be able to combine all the items from both sheets into one summary sheet that i can send to the customer.
I tried consolidate, and messed around with pivot tabels but it didnt seem to be what i'm looking for.
Having trouble opening an access file using this code. Using the following code
View 3 Replies View RelatedI am using the TextBox.Change method to test for numerical input. How can I highlight incorrect input after closing the MsgBox? The following code doesn't always work:
If Not IsNumeric(TxtStage1.Value) Then
Response = MsgBox("Numerical value required", vbExclamation + vbOKOnly, "Invalid input")
TxtStage1.SetFocus
TxtStage1.SelStart = 0
TxtStage1.SelLength = Len(TxtStage1.Text)
End If
I am using something like this to copy data from one workbook to another
View 2 Replies View RelatedI 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 RelatedI need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
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.
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
I'm working on a sheet where by I need to reference multiple columns of information to calculate a total.
Such as (simplified example of my real issue):
__A__B__C
1 20 AP1 C30
2 25 AP1 C30
3 35 AP2 E10
4 50 AP3 E10
My requrement is to only add values in column A wich have corresponding column B="AP1" AND column C="C30"
I've tried the following and keep getting errors:
=SUMIF(AND(C1:C4,"C30"),(B1:B4,"AP1")),A1:A4
I'm trying to do this:
=SUMIF(B5:B12,OR("=D*","=?D*"),D5:D12)
It returns zero each time.
(BTW,
=SUMIF(B5:B12,"=D*",D5:D12)
and
=SUMIF(B5:B12,"=?D*",D5:D12)
both work just fine and return non zero sums.)
So my question is,
Where do I put the OR, or is there another way to do this?
I have a workbook which I am trying to clean up and make more efficient (in terms of formula calculating time).
In it are examples of array formula such as this one (which work correctly):
{=SUM(IF(Expected!$A7:$A894=5,Expected!$K7:$O894))/3}
From past readings here, I have heard that the array {SUM(IF(...} is slower in calculating than the SUMIF(...) equivalent.
As such i tried to convert is to:
=SUMIF(Expected!$A7:$A894,5,Expected!$K7:$O894)/3
This gives a different answer though. I thought for a single criteria they would be equivalent.
Q1: What am I doing incorrectly above?
Q2: Is there a more efficient version of the array formula I could use (SUMIF and otherwise), so I can have some options availible.
I got a column A containing a alpha-numeric data and want to sum up the numeric value of each cell which begins with "t" and have the sum divided by 486 (i.e. (120+230+25)/486). I tried the following formula but to no avail.
A
t120
t230
m45
m30
t25
My formular: sumif(mid(a1:a5,2,3,">0"))
I have worksheet of data. I have another worksheet that is used just for a summary. This data is mapped to a "Job number" (1123646, 1256974, etc.) The first two numbers determine what area it is referring to.
Each column represents a different amount (month to date cost, month to date profit, year to date cost, year to date proift, etc.) I need to summarize each column for each area and display the result on the other worksheet.
I can't use exact ranges like = sum(a9:a31) b/c that range will change each time the worksheet is used. I thought maybe using a loop, but then I got stuck and don't know where to go from here. Here is what I have so far.
Sub SumProfit()
Dim C As Range, sh1 As Object
Application. ScreenUpdating = False
Set sh1 = Sheets("JB-PRF")
Set C = Range("A9")
I don't want to abuse LOOPING or VBA for that matter, but I don't know of any other way to accomplish this seeing as how the ranges will not be the same each time. the shee that the data should be extracted to is named SUM-PRF
I am trying to sum the total of 10,000 lines in column B if column A is 0. I cannot get sumif to work. do I use a different formula?
View 6 Replies View RelatedI know this is a dumb question but I can't get a sumif to work in vba. I want the actual formula in the cell, not just the output value.
This is my most recent attempt:
VB: Range("C2").Formula = "=SUMIF(Z7:Z12000, >0,N7:N12000)"
I get Run-time error 1004, application defined or object defined error.
Why doesn't this work?
I have this from FDIBBINS working if data is on column D and E
=COUNTIF($E$2:$E$4,A2)
How would the same count function if there is a column between d and E
as attached ?
Column A has employee Numbers . ( May repeat , not necessarily specific no.of times )
Column B has date of Joining
Column C has effective date ( There is certain amount to be paid to that employee , from that date until it is revised to a new value )
Column D to - Column H has Months .(Jan-2013 to Jun 2013 ) with the amount to be paid as stated above.
What is the total amount to be paid for an employee considering the updated values .
As shown in the excel , coloured cells are to be summed up for an employee (ex:- Emp.No 1 , Emp. No 2 )
Emp. NoDate of JoiningEffective Date Jan-13Feb-13Mar-13Apr-13May-13Jun-13
11-Dec-12 1-Dec-12100110000400010003000 500
21-Jan-131-Jan-13102112100484012103630605
11-Dec-121-Apr-1300010003993666
32-Feb-131-Feb-13014641585614644392732
11-Dec-121-May-1300005315886
43-Jun-131-Jun-1300000974
[code].....
Here is the Formula.
=SUMIF(***!$F$6:$F$951,B16,***!$G$6:$G$951)
excel screen shot.PNG
This is the page it is referencing to.
PO screen shot.PNG
This is where the formula is. It is in "I16"
What I want to do is Take this and add in the multiplier from column "H" in the "***" page. I need to change the name of that tab, I am not trying to be foul. I just use that short for assembly. It looks bad, but usually, I'm the only one that sees it.
Basically, I want to take the material qnty and multiply it by the multiplier and put that in the box on the po page that accumulates the material.
I am writing the sumif function in F2 but it is not working correctly..
View 14 Replies View RelatedI am having a problem with a formula I think I should be able to get correct but not sure if I am able to do so. On the attached file I have a filter on the TIER's for "Is grater than or equal to 5". What I need is a SUMIF formula that will take into account the filters. This formula needs to separate out between "GER", "IRE" and "UK" in cells C37, C38 and C39.
I have a subtotal in cell C35 which gives me the subtotal of all countries but I'd like to be able to have the subtotal separated out between the 3 countries and also still have the ability to manipulate the data so I could select different TIER's or a range of TIER's and Cells C37 - 39 automatically update themselves.
I have an entire excel column which was filled-in with values ranging from 1000 to 40000. What I was trying to do is to just sum up the all the values which are between 9000 and 20000. I tried using the sumif function =sumif(and(A5:A40,">9000"),(A5:A40,"<20000")) but it does not work, it says that the formula that I typed contains an error.
View 4 Replies View RelatedI am trying to use SUMIF as part of an IF statement, What I am trying to do is, for example, if a column has two values M and P, then SUMIF only those rows that have a corresponding M, and not calculate the P's.
View 9 Replies View RelatedI 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 RelatedI am trying to used the sumIF function in refer to a particular value, e.g. Name 1.
Question 1 - What formula I can used to make sure the SUMIF function refers to that specific value, e.g Name 1?
Question 2 - Is my SUMIF function correct to calculate the SUM of hours to a Task? This is due to the data table setting? find attached:
I'm trying create a Sum(If formula with 2 conditions, but whatever I try doesn't seem to want to cooperate. I want to add up the sum of all invoices according to the country they deal with and the month that they were created in. I've attached a sample file showing what I'm trying to accomplish. Here is how far I've gotten so far: =SUM(IF(Main!$L:$L;B21*Main!$Z:$Z;C20);Main!$H:$H)
I've tried other forms as well but even though this only returns #Value!, at least the formula is accepted.
I have an old version of Excel and want to use sumifs()
This means I want to add data based on 2 conditions. What should I do?