{SUM(IF(...} To SUMIF(...)

Apr 4, 2009

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.

View 9 Replies

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

I 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 Related

Nested SUMIF Statement Or Multiple SUMIF

Sep 17, 2009

I 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.

View 6 Replies View Related

SUMIF In A Column: The First Instance Of Each SUMIF

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

SUMIF And SUMIF Not - Using Two Formulas In One Cell

Feb 27, 2012

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?

View 2 Replies View Related

Using SUMIF + AND (if Possible)

Jan 14, 2009

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

View 5 Replies View Related

Sumif With Or

Oct 12, 2005

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?

View 14 Replies View Related

About Sumif And Mid

Oct 19, 2009

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"))

View 9 Replies View Related

SUMIF Or VBA Or Both

Feb 18, 2006

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

View 9 Replies View Related

Sumif

Jan 18, 2007

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 Related

Converting Sumif To VBA

Nov 26, 2013

I 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?

View 2 Replies View Related

Sumif With Not Columns Far Apart

May 5, 2014

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 ?

View 1 Replies View Related

Sumif / Lookup For A Value And Sum

Dec 4, 2013

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].....

View 1 Replies View Related

Can Add Multiplier In Sumif?

Feb 26, 2014

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.

View 14 Replies View Related

Excel Bug ? For SUMIF

Dec 15, 2008

I am writing the sumif function in F2 but it is not working correctly..

View 14 Replies View Related

SUMIF With Subtotals

Jan 5, 2009

I 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.

View 3 Replies View Related

Sumif Functions

Jan 8, 2009

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 Related

Combine SUMIF With IF

Jan 23, 2009

I 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 Related

SUMIF With Criteria

Feb 6, 2009

I 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 Related

SUMIF Function To A Particular Value

Apr 17, 2009

I 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:

View 3 Replies View Related

SumIf With More Than One Condition

May 12, 2009

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.

View 5 Replies View Related

Multiple Sumif( )

Jun 14, 2009

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?

View 9 Replies View Related

SUMIF 2nd Criteria

Jun 15, 2009

Im using a formula to identify a job description then add up the quantity which works fine. =SUMIF(Master!A1:A10,"Bread",Master!C1:C10)
But my problem is, I have a second description in column 'B' for example called 'White'. I've been trying to get the formula to look at column A & B and if they match criteria in formula, then add up C. But can't seem to get it to work with the second criteria.

View 2 Replies View Related

Sumif Between Certain Numbers

Aug 31, 2009

How can I do this?

F9= 322,539.77
D4=263,800
E4=.01
F4=1319.00

I want to say,

if F9 falls between 0 and 137099 dollars, the answer is D2.

If F9 falls between 137,100 and 263,799 then take F9-D3*E3+F3.

If F9 falls between 263,800 and 527,599, then take F9-D4*E4+F4

I tried this, but obviously it didn't work:
=SUM(IF(F9<=137099,52),(IF(F9<=263799,F9-D3*E3+F3),(IF(F9<=527599,F9-D4*E4+F4))))

So if calculated correctly, the answer should come up to 1906.40

View 9 Replies View Related

VLOOKUP Or SUMIF Or

Sep 22, 2009

I have a workbook containing 2 tabs.

Tab 1 has columnar data with column A containing locations; however, each location may be listed multiple times on different lines - once for each piece of equipment at that location. Tab 1, column B has a piece of equipment listed. Tab 1, column C has a price associated with the piece of equipment in column B.

Tab 2 contains contains columnar data with column A containing locations. I need to populate Tab 2, column B with the sum of all equipment associated with the location in column A.

I have used VLOOKUP to try and find matches for the lookup_value in Tab 2, column A, then return the value of the price from Tab 1, column C.

Problems have been that it returns only the first instance of the location data, not a sum of all. I know the formula is missing something, but cannot ID it.

Formula as used:

=VLOOKUP(B4,'Tab 1'!$I$4:$AI$461,3)

B4 is the column on Tab B that contains all of the locations (lookup_table)
Tab 1 I4 thru AI461 is the table_array where all locations are listed
3 is the col_index_num indicating that the price is in the 3rd column over

View 14 Replies View Related

Alternative To SUMIF

Jan 21, 2010

I'm using sumif to get the total number of OT minutes/hours rendered by an employee. In a company with 1K employees, doing so proves to be tedious as I need to rename the criteria for each formula. Is there an easier way to do this? See attachment for more details. I'm using Excel 2003.

View 2 Replies View Related

SUMIF With Thresholds?

Dec 27, 2012

I have set of thresholds to which a value is corresponding. What I want to have is to get a corresponding value based on any input that falls within that threshold. I attached workbook which will probably be clearer.

I believe it's a SUMIF function with some &/>/< etc.

View 5 Replies View Related

Reference Last Row In Sumif VBA

Mar 20, 2009

I recorded this sumif in VBA, but know I am trying to figure out how to tie in the last row reference in place of the 27.

=SUMIF($AR$2:$AR$27,$AR29,AS$2:AS$27)
FormulaR1C1 = "=SUMIF(R2C44:R27C44,RC44,R2C:R27C)"

View 4 Replies View Related

Sumif Function.?

Jul 14, 2009

I am using Excel 2003. Worksheet named: Table_Data has the following code in cell: B2

View 3 Replies View Related

Sumif Hit A Blank

Oct 24, 2007

I am wondering if someone could assist me as I have hit a blank. I have a sumif statement that is working perfectly with identifying one set of criteria, but now I want to return the sum of a range referencing two other range conditions.

View 6 Replies View Related