Convert SUMIF Formula Into A VBA Code


I've been trying to modified the formula below into the code you see below. = SUMIF(G3:G21,">0",G3:G21)-SUMIF(A3:A21,"=",G3:G21) So how can to modified the SUMIF formula into a code to the finalrow? Range("J" & FinalRow + 1).Formula = "=SUM(J2:J" & FinalRow & ")"

View Answers
Very similar questions



Macro To Fill Column With Relative Formula www.ozgrid.com

I want my column to be populated by formula using VBA. Let's say on row1, the formula is = SUMIF(mysheet!$L:$L,$A1&C$1&"09",mysheet!$M:$M). And on row 2, the formula is =SUMIF(mysheet!$L:$L,$A2&C$1&"09",mysheet!$M:$M). And on row 3, the formula is =SUMIF(mysheet!$L:$L,$A3&C$1&"09",mysheet!$M:$M). And this is done up to the last row. How do I do this?

VBA Code Quicker Than Just Writing Out The Calculation In VBA www.ozgrid.com

Does Excel handle formulas written into the VBA code quicker than just writing out the calculation in VBA? I have a section where I use the following formulas, sumif, countif and a combo if iserror sumproduct in the VBA code...runs rather slow at this point and was looking at a way to speed things up.

SumIf Function In VBA Code www.xtremevbtalk.com

Hi,I'm trying to write the equivalent to Excel's SUMIF funtion but in VBA code. I tried to rearrange the Excel funtion I initially typed in a recorded Macro but it doesn't seem to work for VBA.This is an example of what I'm trying to do. If Range("G1") = "" Then Range("J1") = "" Else Range("G1") = SumIf(Range("G:G"), Range("G1"), Range("I:I")) End IfI don't want the formula written into the cell in my Worksheet since I've got to many rows and it makes thw Worksheet extremely heavy.Thx,Jack

VBA Code For SUMIF Formulas? www.excelforum.com

I need to run the following formula in multiple excel documents so I want to creat a macro for it, creating the needed code for this. The below formula needs to be inserted in cell B125 =SUMIF(B:B,""IDN"",C:C)+SUMIF(B:B,""MYS"",C:C)+SUMIF(B:B,""PHL"",C:C) +SUMIF(B:B,""SGP"",C:C)+SUMIF(B:C,""THA"",C:C)+SUMIF(B:B,""VNM"",C:C))" The below formula needs to be inserted in cell B126 =SUMIF(B:B,""IDN"",D:D)+SUMIF(B:B,""MYS"",D:D)+SUMIF(B:B,""PHL"",D:D) +SUMIF(B:B,""SGP"",D:D)+SUMIF(B:B,""THA"",D:D)+SUMIF(B:B,""VNM"",D:D))" The below formula needs to be inserted in cell B127 =SUM(B125/B126)

Insert Total Formula Using SUMIF And Named Range www.ozgrid.com

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.

VBA Function Formula Using Sum And If www.excelforum.com

I am using Excel 2007 on Windows XP. I have the following formula that I would like to move from the worksheet into VBA. I am having an issue with the �IF� statement within VBA throwing a compile error for �Expected: expression�. I am assuming it does not like the �IF� statement within the formula. I also provided a sample of the code that I was able to get working with a SUMIF statement but with only one condition.

Converting Sumif To VBA ozgrid.com

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?

Save 10,000 Hours On Manually Changing Formulas www.mrexcel.com

This formula returns the production for a certain month: =+SUMIF(Line2!472:472,D5,Line2!18:18) I am needing to change this formula to show sales forecast... The formulas will be identical EXCEPT the 3rd part of the SUMIF will be 2 rows down. So this one would be :=+SUMIF(Line2!472:472,D5,Line2!20:20) How in the world can I take a formula, and change it for over 4704 entries? I actually tried this: =+SUMIF(Line2!472:472,D5,Line2!18+2:18+2) of course will no prevail.

Absolute/Relative Ranges In Formulas www.ozgrid.com

I have a Formula = SUMIF('Master List'!B3:B190,AE197,'Master List'!AG3:AG190) When I copy this formula the next line is as Follows =SUMIF('Master List'!B4:B191,AE197,'Master List'!AG4:AG191) Is there something I can do to in order to make so that when I copy the formula is would read =SUMIF('Master List'!B3:B190,AE198,'Master List'!AG3:AG190). Elliott Auto Merged Post;Nevermind I used the formula = SUMIF('Master List'!$B$3:$B$190,AE197,'Master List'!$AG$3:$AG$190)

Nested Formula (Sumif / Countif) - Sorting Table www.mrexcel.com

It's actually a sumif/countif that works fine if i sort my table, which i don't want to do. Here's the original formula: Code: (SUMIF(H4:H35,"1",G4:G35)/COUNTIF(H4:H35,"1")*1+SUMIF(H4:H35,"2",G4:G35) /COUNTIF(H4:H35,"2")*2+SUMIF(H4:H35,"3",G4:G35) /COUNTIF(H4:H35,"3")*3+SUMIF(H4:H35,"4",G4:G35) /COUNTIF(H4:H35,"4")*4)/10 I'm trying to apply the formula using some other filters, like if(C4:C35=O5 & I4:I35=M5). I tried creating a SUMPRODUCT out of it but it was just as bad: Code: =(SUMPRODUCT((C4:C35=$O$5)*(I4:I35=$M$5)*(H4:H35=1)*(G4:G35)) /SUMPRODUCT((C4:C35=$O$5)*(H4:H35=1)*(I4:I35=$M$5)*1)*1+... And also this with sum(if) Code: =(SUM(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35)))) /COUNT(IF(C4:C35=$O$5,IF(I4:I35=$M$5,IF(H4:H35=1,G4:G35)))))*1+... Is there a shorter formula that I'm not seeing ? I'm also confronted with the need of adding an "ISERROR" in the formula because not all ranges have the 1-4 numbers in column H.


Add Update To All Formulas In Selection www.mrexcel.com

I have quite a few formulas on my sheet that I need to add a function. Basically I have =SUMIF($I$2:$I$30,I1,$J$2:$J$30) and it needs to be update too and I need =IF(Master!G5="","",SUMIF($I$2:$I$30,I1,$J$2:$J$30)) Is there a VBA routine that can do this?

Compound SumIf() Conditional Formula www.xtremevbtalk.com

I'm looking for what would be the equivelant to a compound SumIf() function that can be used in a formula.For example:The cells in a given Range (ie; E4:E25) can contain 1 of 3 different code values... "AB", "BW", and "RW"The cells in a given Range (ie; G4:G25) can contain 1 of 4 different code values... "E5050NSTD", "F5050NSTD", "G5050NSTD", and "H5050NSTD"The cells in another Range (ie; H4:H25) contain a numeric value.If I wanted to calculate the sum of values from the Range H4:H25, whose corresponding E cells contained "AB", then I'd use the formula:=SUMIF(E4:E25,"AB",H4:H25)Likewise, if I wanted to calculate the sum of values from the Range H4:H25, whose corresponding G cells contained "F5050NSTD", then I'd use the formula:=SUMIF(G4:G25,"F5050NSTD",H4:H25)What I am having difficulty with, is creating a formula that would take two conditions to produce the sum of values from H4:H25.In psuedo-code:=SUMIF(E4:E25="AB" AND G4:G25="F5050NSTD",H4:H25)Suggestions?

Sum By Criteria Across Multiple WorkBooks www.ozgrid.com

Is there a shorter way of doing this formula using sumif on Excel 2002? This formula works but only up to worksheet 13, need it to go through 31. =SUMIF('1'!A4:A41,"12C",'1'!E4:E41)+SUMIF('2'!A4:A41,"12C",'2'!E4:E41))+SUMIF('3'!A4:A41,"12C",'3'!E4:E41).............

SUMIF Of Multiple Values www.mrexcel.com

I want to do a SUMIF formula and add only certain words in column A. Column A has a list of airlines in it, and column H has a list of flight durations. I want to add together all of the flight times but for only a few airlines. I tried to put OR in the middle of the SUMIF function, but it didn't work: =SUMIF(C:C, "Air Canada" OR "Air Tahtiti Nui", H:H) How would I get it to add together all of the flight times for Air Canada and Tahiti Nui, without having to put many SUMIF formulae?

Change Formula Range Without Changing The Contents? ozgrid.com

I'm using the formula =SUMIF(F18:F18,"LY",G18:G18), =SUMIF(F18:F18,"KT",G18:G18), and so on. I want to change the range to: =SUMIF(F7:F18,"LY",G7:G18), =SUMIF(F7:F18,"KT",G7:G18), and so on. I can do it manually, but it's 20 rows. Is there a way where i can change the formula range on the first and copy it down, without also copying the initials, (etc. "LY") ?

SUMIF Formula - Range To Vary www.xtremevbtalk.com

I need the sumif function to vary with the value of a month cell that i can select.My formula (for January) looks something like: Code:= SUMIF (Sheets("A").Range("C"),"=Department A",Sheets("A").Range("D"))For February, I would want it to sum Range("G") iso Range("D").The trend in between months is +3 columns.How can I put a variant into this formula?Also, formula does not seem to work using Sheets(...).Range(...) kind of reference... How can I make it work?

Check Whether All The Formulas Are Dragged Down Correctly www.excelforum.com

How to create a macro tool to check whether all the formulas are dragged down correctly? Because someone may accidentally make some changes to some cells. For example, A1=IF(NOT($B1), -SUMIF(PCAInfo'!$C1, AQ$10:AQ$150),0) A2=IF(NOT($B2), -SUMIF(PCAInfo'!$C2, AQ$10:AQ$150),0) A3=IF(NOT($B4), -SUMIF(PCAInfo'!$C1, AQ$10:AQ$150),0) A4=IF(NOT($B4), B4-SUMIF(PCAInfo'!$C4, AQ$10:AQ$150),0) The code could test out A2 is correct, A3 and A4 is not correct.

Dynamic SumIF Formulas That Reference Changing Codes www.mrexcel.com

I have a list of codes from which I'm trying to extract all unique entries. For example, col A has 001, 001, 002, 003, 003, 003, 004, ... I'm trying to create a report template with formulas that reference col A and return a single unique entry for each code so that the new column has only 001, 002, 003, 004. Essentially, the codes reference customers. A customer can have multiple purchases -- but I'm trying to create a report in which I can utilize SumIf formulas to Sum the purchases for each customer and represent those purchases on a single line per customer code. The catch is that the purchases change monthly (i.e., next month the purchases may be 001, 002, 002, 002, 004) so my report template needs to have the flexibility to adapt without me rewriting the SumIf formulas and return SumIf results for only customers 001, 002, 004. Whereas in the prior month, the report returned SumIf results for customers 001, 002, 003, 004.

Sumif In Vab www.xtremevbtalk.com

I tried this code Code:Range("A4").Formula = "=SUMIF(N10:N65530, ,"George",R10:R65530)"It showme this because of the string "George" Compile error:Expexted : end of statementI tried another way by Code:Range("A4").Formula = "=SUMIF(N10:N65530, George,R10:R65530)"But it doesn't workHow to fix this problem

Sum If Query: Return A Value Where A % Is Used www.ozgrid.com

I have been using the following formula to return a value where a % is used, is there anyway i can make the sum shorter. =SUMIF(Q10,'Price Calculator'!$A$5,'Price Calculator'!B5)+SUMIF(Q10,'Price Calculator'!$A$6,'Price Calculator'!B6)+SUMIF(Q10,'Price Calculator'!$A$7,'Price Calculator'!B7)+SUMIF(Q10,'Price Calculator'!$A$8,'Price Calculator'!B8)+SUMIF(Q10,'Price Calculator'!$A$9,'Price Calculator'!B9)+SUMIF(Q10,'Price Calculator'!$A$10,'Price Calculator'!B10)+SUMIF(Q10,'Price Calculator'!$A$11,'Price Calculator'!B11)+SUMIF(Q10,'Price Calculator'!$A$12,'Price Calculator'!B12)+SUMIF(Q10,'Price Calculator'!$A$13,'Price Calculator'!B13)+SUMIF(Q10,'Price Calculator'!$A$14,'Price Calculator'!B14)+SUMIF(Q10,'Price Calculator'!$A$15,'Price Calculator'!B15)+SUMIF(Q10,'Price Calculator'!$A$16,'Price Calculator'!B16)+SUMIF(Q10,'Price Calculator'!$A$17,'Price Calculator'!B17)+SUMIF(Q10,'Price Calculator'!$A$18,'Price Calculator'!B18)+SUMIF(Q10,'Price Calculator'!$A$19,'Price Calculator'!B19)+SUMIF(Q10,'Price Calculator'!$A$20,'Price Calculator'!B20)+SUMIF(Q10,'Price Calculator'!$A$21,'Price Calculator'!$B$21) Seems like a Pain to be using something this big, just cant seem to get it to work when i try to shorten it

Convert Excel Formula - Equivalent For A Form? www.codingforums.com

I am trying to convert this excel formula into a Javascript equivalent for a form I am doing in Acrobat Pro 9 and you guessed it. everything is done apart from this. =SUMIF(B2:B29,"LA",A2:A29)+SUMIF(F2:F29,"LA",E2:E2 9)

Evaluate Multiple Functions In The 1 Cell www.ozgrid.com

When using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula? E.g. say you have: = SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207") And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part? Im using Excel 2003 by the way.

Finding Volume Total In Range www.mrexcel.com

I have this formula below which works for finding the Volume Total in range C7:E7...it displays the volume for that date in G7 Code: =SUMIF(INDIRECT("'"&E$6&"'!B7:B10000"),$G$7,OFFSET(INDIRECT("'"&E$6&"'!B7:B10000") ,,MATCH($E10,INDIRECT("'"&E$6&"'!$C$7:$E$7"),0),,)) but when I use the formula for finding the PLanned Stops - Time in the range G7:H7 it gives the Volume Total from Column D...I want the Planned Stops -Time from H7..... Code: =SUMIF(INDIRECT("'"&E6&"'!B7:B10000"),G7,OFFSET(INDIRECT("'"&E6&"'!B7:B10000") ,,MATCH(E13,INDIRECT("'"&E6&"'!G7:H7"),0),,))

Sumif Function To Find And Collate Unqiue Data On Different Worksheets www.ozgrid.com

Most excel/vba books say not to use the sumif function. I have a very large workbook (9meg) and cant use VBA code as it slows down the interation calculations required. I have used the sumif function to find and collate unqiue data on different worksheets. Is there another function that i should be using?

Add AVERAGE Formulas To Cells Using Variables In Macro Code www.ozgrid.com

I am trying to have the formula =( SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Put into cells through vba. What I did to get the formula is typed it into an excel cell to find the average of a group of cells that do contain blank cells. The formula brought out the proper results. So all I did is put the formula into vba and changed the appropriate parts. The range will not be the same of course, but there is what I have. ActiveCell.Formula = "=(SUMIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "," & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & "))/(COUNTIF(" & ActiveCell.Offset(orow + 2, 0).Address & ":" & ActiveCell.Offset(-1, 0).Address & ","">0""" & "))" When I show a msgbox for ActiveCell.Formula (Msgbox activecell.formula), it shows me the formula as above - =(SUMIF(S2:S125,">0",S2:S125))/(COUNTIF(S2:S125,">0")) Except instead of the s:ranges, it shows $L2:$L125 (which is correct). The quotes do show up around the criteria in both the sumif and countif. I keep receiving an error. I put a msgbox err.description & ", " err.number dialog in. The error comes up as ", 0" (no quotes).

Sumif(range,30,sum Range) Not Summing Values That Equal 0 www.excelforum.com

I have a simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.

{SUM(IF(...} To SUMIF(...) www.mrexcel.com

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.

Excel Formula. www.xtremevbtalk.com

Hi this has been driving me mad all day , Im new to this but Im sure the solution is simple.I recorded a macro, a sumif formula. In VB its translated to Formula R1c1 which I dont mind however the original formula was relative (ie $a$1)Now in VB I looped the sumif to fill a range of values using 'dim if' this works fine however the formula does not remain relative and I can't work out how to make it so.Any help would be enormously greatful.

Sum Range By Font Italics www.ozgrid.com

Not sure if this can be done, but is there a way I can write a sumif formula to add up all amounts which are in Italics? I dont want to use VBA, just a simple excel formula. Is this possible?

Sumif Which Returns Multiple Columns www.mrexcel.com

I've noticed recently that a sumif formula will only ever add up 1 column's worth of data. Is it possible to get it to do multiple columns? For eg, I have a spreadsheet that looks like: Using the formula shown, I can easily add up values for 'A' codes for one of the 3 columns, but short of adding together multiple sumif's, there must be a more intelligent way of doing it?

Convert Formula Into VBA Code www.excelforum.com

Below is my requirement and i have to convert formulas into vba code: If dt = "A0" Or dt = "A1" Then Sheet1.Range("K").Formula = "$J/$ I" ' Formula is k=J/I Sheet1.Range("K:K" & last_row).FillDown Sheet1.Range("L").Value = "20.00%" Sheet1.Range("L:L" & last_row).FillDown //Formula is L=20% for now i have just hardcoded the value there. Sheet1.Range("M").Value = "=($K-$L)* 100" 'Formula is M=(K-L)/*100 Sheet1.Range("M:M" & last_row).FillDown End If

Adding Formula To VBA Code? www.mrexcel.com

I have the following code which works plus a formula in cell range G5:G123....how do i add the the formula to the VBA code Formula =IF(F5"".F5< today () +3 )

Change Formula Based On Cell Value In A1 www.ozgrid.com

= SUMIF(SHIPPED!$A$3:$A$13,A2,SHIPPED!$B$3:$B$13) I would like for the formula to change to what is in A1.. If I change A1 to C, I would like for the formula to replace A with a C in the formula. SUMIF(SHIPPED!$A$3:$A$13,A2,SHIPPED!$C$3:$C$13)

Sumifs With Index/Match Column www.excelforum.com

I have used this technique before with a sumif formula, to use the column that matches a reference cell but I cannot get this to work on a sumifs formula. What I have used previously in a sumif formula (only 1 criteria) is below.

SUMIF Using Greater/Less Than Symbols In Text Criteria www.ozgrid.com

I'm trying to get a simple Sumif formula to work. I have used this formula a hundred times before but this time the results are inaccurate. I suspect that it may be because of the math symbols which are used in the criteria text i.e. < and > My formula is as follows: =SUMIF('Raw Data Export'!D:D,"< No Project >",'Raw Data Export'!G:G) A data extract from the sheet named "Raw Data Export" is attached.

Alternative For SUMIF Formula www.excelforum.com

The below formula is giving the desired result and everything seems fine. Need if there is an alternative formula which can give same result in more refined and simplified manner. =K28+SUMIF($E$3:$E$24,$E$7,$D$3:$D$24)/(K23)-SUMIF($B$3:$B$24,$B$8,$D$3:$D$24)/(K23). The formula is based on the following data: K28 = Last month balance Column E = Type of expenditure Column D = Values Column B = Payments K23 = Currency conversion rate

Sumif Formula The Meets Five Different Criteria www.mrexcel.com

I want to create a sumif formula that will sum the data if it meets five different criteria. I tied to do an �Or� statement in the formula, but it doesn�t work. For example, I want to sum all the rows that contain: Apples, Bananas, Cherries, Pears, and Plums. How do I write the sumif formula so that it will do this?

SumIf In Macro Code www.ozgrid.com

I need to use a SUMIF formula in a macro. however the columns in the range are variables. I can easily find the columns numbers but I can't find the way to embedded them in the formula. (For example : if the formula is : =SUMIF(A:A,A1,K:K) column "K" may change to to "B").

Multplie Sumif Formulas With Division www.ozgrid.com

I have this formula. I'd liked the entire referenced amount(s) to be divided by 2. Nothing I tried worked. = SUMIF( 'X'!$B$1:$B$3000;'Z'!$B34;'X'!$F$1:$F$3000)/7,6+(SUMIF('X'!$B$1:$B$3000;'Z'!$B34;'X'!$G$1:$G$3000)/7,6)+SUMIF('Y'!$B$1:$B$3000;'Z'!$B34;'Y'!$F$1:$F$3000)/7,6+(SUMIF('Y'!$B$1:$B$3000;'Z'!$B34;'Y'!$G$1:$G$3000)/7,6)

Change Cell Address In Formula On Cell Value Change www.mrexcel.com

I have below formula.i need to change the cell address AK in the below formula on the value change in the A3. =SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AK$6:$AK$3000) For example if A3 is having value AZ i need the formula changed automatically as below =SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AZ$6:$AZ$3000)

How To Use Date Range In SUMIF Function www.mrexcel.com

I am trying to use a date range as a criteria in a SUMIF function. Below are the data and formula I am using. Forecast 6 4 15 8 Week 1/12/2014 1/19/2014 1/26/2014 2/2/2014 Formula =SUMIF(G2:J2,">L1",G1:J1) G1:J2 - raw data shown above. L1 = 2/2/2014. Desired result = 8. Result obtained with formula above = 0 how to make this work?

Using Sumif And Indirect To Reference Another Worksheet www.mrexcel.com

I am trying to use the sumif and indirect function together but I keep getting an error and I'm not sure why. Actual formula: =SUMIF('SWG OR'!B:B,A168,'SWG OR'!E:E) Using the sumif/indirect function, I have the following: > Cell B5 contains the name of the tab "SWG OR" =sumif(indirect(B5&"!B:B",A168,B5&"!E:E"))

Developing SUMIF Formula Over Multiple Variants? www.mrexcel.com

SUMIF function. I am looking to build on this original formula but neither the SUMIF or SUMIFS formulas are working...here what it is: I am looking to create a formula that will all up the figures based on the criteria within a specific date range - for example by selecting Company A and Workshop in Feb 2011 it will return £1678.75 I've also tried IF functions within SUM functions but can't get it to work.

Sumif With Indriect: Which Column To Sum www.ozgrid.com

I am trying to setup formula so I can enter column letter and sumif should update with proper sum range to be summed ...

Convert Column Letter To Number ozgrid.com

I need to convert a column letter to a column number for the following line of code in my VBA application: VB: strAverageBaseFormula = "=AVERAGE(D" & Trim(Str(lngFirstRowOfIncrement + 1)) & ":K" & Trim(Str(lngFirstRowOfIncrement + 1)) & ")" I am inserting a formula into a cell using VBA, but the problem is that I don't know the letter value of column "K" (used in the middle of the line); instead, I know it as column number 11. Is there a simple way to convert the column number 11 to the column letter "K"?

Combining Multpile IFs www.mrexcel.com

Individually these formulas work - is there anyway I can combine them into one formula in one cell so the will look at the month and then perform SUM function? =IF(I$2="apr",SUM(SUMIF('Raw Data'!$A$1:$A$64773,$A$1,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$74,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$75,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$76,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$77,'Raw Data'!D1:D64773),SUMIF('Raw Data'!$A$1:$A$64773,$A$78,'Raw Data'!D1:D64773)),"X") Etc for all 12 months?

Converting Text To Number www.excelforum.com

I have a formula which extracts a number from a text string. The text string is in A2 - the formula which extracts the number is in B2 However - when I copy the data in B2 and paste values to another sheet / cell - the forumla is as text - and I get the little green corner symbol and I have to convert to number. Some VBA code - that will automatically convert my extracted "number" - to an actual number in number format.

Excel 2007 :: Determining / Counting Number Of Values Used In SUMIF / SUMPRODUCT Function www.mrexcel.com

I am using Office/Excel 2007 and Windows 8 I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively. Example: Results for Row #2 gives: 95 with 3 values picked up under "Rate" Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate" How can I count the number of these values referencing at the "Rate" like in the functions used. I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one. I tried COUNTIF function, but allows only field/heading. A B C D E F G [Code] .......... results for each formula used 95 formula: =SUMIF(B$1:G$1,B$1,B2:G2) 95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2)) 3 formula: =COUNTIF(B1:G2,B1)

SUMIF - Using Multiple Criteria (10 To 20 Values) www.mrexcel.com

Sumif formula. I need to somehow put a loop on the criteria or something... The formula is sumif(range, criteria, sum range). The problem I am having is that I have about 20 criteria to select, and sumif is designed for one criteria. I know that I can type the exact same formaula in and have something like this... =SUMIF(D25:D107,Variables!A6,F25:F108) + SUMIF(D25:D107,Variables!A7,F25:F108) + SUMIF(D25:D107,Variables!A8,F25:F108) + SUMIF(D25:D107,Variables!A9,F25:F108)+ SUMIF(D26:D107,Variables!A10,F26:F108)+ SUMIF(D26:D107,Variables!A11,F26:F108) This is OK for a few variables but some formulas have multiple criteria in excess of 15-20. the other thing is that the criteria list will grow over time as more variable are added, and instead of changing the formulas throughout the spreadsheet, I would like to have a big range that I can slowly fill up as I go along The ideal situation would be if I could put a range in ( ie A6:A30) and then when I get a new criteria I just add it to this range. This will make administration eaaseier by giving me one location to update data. I will show you an example.. I want to calculate the amount of petrol I use based on three criteria - BP, Shell, Caltex ( the criteria being petrol stations that come up on my bill). Column A/ B / C/ D Row / description / Amount/Variables 1 / Shell Petrol / $10/Shell 2 / Cat Food/$13/BP 3 / BP Store/$24 / Caltex 4 / Dog Food/$23 5/Shell Petrol / $98 6/Caltex Petrol/$31 7/BP stuff/$30 8/Shell Store/$70 What I have been doing is this... =sumif(B1:B8,D1,c1:c8) + sumif(B1:B8,D2,c1:c8) + sumif(B1:B8,D3,c1:c8) If I could somehow get the formula to choose the whole column D as the criteria range and get the 'sum if' to somehow loop so it goes down the d column and does the same calculation, but just changes the criteria variable each time, then instead of changing the formula, I could just add to the D column each time I had a new criteria I wanted to check. I looked into DSUM and Pivot Tables. DSUM seems to work well with numbers, not variables and Pivot tables did my head in, especially seeing I know that there is a way to do this in a formula based way..

Summing Numbers Only If They Correspond To Another Column? www.excelforum.com

I added an attachment. Column A is my "week number" and column E is my sales amount. I'm trying to create a formula that sums the sales amount, only if it corresponds to a certain week number. I know sumif formulas, but can't get them to work well for this. For example, I want it to sum all the sale that happened in week one, then all the ones in week 2, then all the ones in week 3, and so on. There has to be an easier way than doing a sumif for each individual week and manually changing the week number in the sum if formula. Could there be a character you can use in a formula to make the sumif week number "running?" So you could then just type in the formula once and pull it all the way down for the year, so the formula will work for the every week throughout the year?

SumIf Function In VBA Code www.xtremevbtalk.com

Edit by loquin: Original Thread was more than a year old. Please review the Posting Guidelines(PLUS, the answer IMMEDIATELY followed in post #2...) Quote:Originally Posted by GoodoldJackHi,I'm trying to write the equivalent to Excel's SUMIF funtion but in VBA code. I tried to rearrange the Excel funtion I initially typed in a recorded Macro but it doesn't seem to work for VBA.This is an example of what I'm trying to do. If Range("G1") = "" Then Range("J1") = "" Else Range("G1") = SumIf(Range("G:G"), Range("G1"), Range("I:I")) End IfI don't want the formula written into the cell in my Worksheet since I've got to many rows and it makes thw Worksheet extremely heavy.Thx,Jack Hi ............ I'm trying to solve the same problem. What was the answerThanks in advance

SUMIF Over Multiple Discrete Ranges www.xtremevbtalk.com

Hi All,I have been having a few headaches trying to sort out a problem with SUMIF in my code (Excel 2000 VBA). After playing around for a while, I realised the range was a varying number of discrete groups of cells of varying size, which SUMIF isn't setup to handle.While I found another way around the problem, I guess I'm just curious if there is a method of handling this inbuilt in VBA (for example array formulas), short of writing a UDF. Sure, it wouldn't take long to write a UDF for it, but I am just wondering if this functionality is included as I imagine it would come up fairly regularly, and would be more efficient than a UDF.Cheers,Dangles

Vlookup In Macro Code www.ozgrid.com

convert this formula to VBA. The are four formulas in there: 1. lookup formula. 2. remove the 1st number (exmaple: remove 0 in 0123 to become 123) 3. add a number (example: add 0 to 123 to become 0123) 4. convert a number into text format.

Sumif Across Multiple Columns www.mrexcel.com

I'm trying to sumif across multiple columns, only if there is NO data in the first cell of the row. Column A is not in use - B contains some blank cells and some with data. In columns C:AM I want to sum all data in rows where the cells in Column B are blank. Code: =SUM(SUMIF($B$2:$B$191,"",C2:C191),(SUMIF($B$2:$B$191,"",D2:D191)),(SUMIF($B$2:$B$191,"",E2:E191)), (SUMIF($B$2:$B$191,"",F2:F191)),(SUMIF($B$2:$B$191,"",G2:G191)),(SUMIF($B$2:$B$191,"",H2:H191)), (SUMIF($B$2:$B$191,"",I2:I191)),(SUMIF($B$2:$B$191,"",J2:J191)),(SUMIF($B$2:$B$191,"",K2:K191)), (SUMIF($B$2:$B$191,"",L2:L191)),(SUMIF($B$2:$B$191,"",M2:M191)),(SUMIF($B$2:$B$191,"",N2:N191)), (SUMIF($B$2:$B$191,"",O2:O191)),(SUMIF($B$2:$B$191,"",P2:P191)),(SUMIF($B$2:$B$191,"",Q2:Q191)), [Code] ...........

Reference Last Row In Sumif VBA www.excelforum.com

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

Looking Up A Sum Of Data In Several Columns For Specific Values Of Another Column www.excelforum.com

=(SUMIF($J:$J, "Player1",$K:$K)+SUMIF($L:$L,"Player1",$M:$M)+SUMIF($N:$N,"Player1",$O:$O))-SUM($I:$I) This looks up if Player 1 was in the top 3 scoring zones and adds up the players total score. I have another column in this table of data labelled 'Number of Players'. I was wondering how to lookup the total score for this player in the games he played under 6 players for example and the games he played in where there were other six players. I've tried using that formula ^^ with variations of IF and SUMIF formulas to try and figure it out, but I keep getting formula errors or it just returns with the total score, and not just with the total score under 6 players. I am also trying to create a graph of date on the x axis against score to date on the y axis - everytime I try, the x axis is always numbers instead of the date?

Can Sumif Accepts More Than 2 Sum_range www.mrexcel.com

This is my excel sheet Cell A CellB Cell C Name Hour Work Hour Rest Alan 3 1 Alan 5 0.5 Steven 2 0 Alan 3 1.5 Ricky 5 0.5 Steven 3 0 If I use sumif using VBA, I would have to Evaluate("sumif(Sheet1!A:A,""" & "Alan" & """, Sheet1!B:B)") Evaluate("sumif(Sheet1!A:A,""" & "Alan" & """, Sheet1!C:C)")

Keep Formula Same For Entire Column? www.mrexcel.com

I'm inserting new information at the top of my worksheet. Unfortunately the formula for what WAS row one: =SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) Will no longer work for row one because as more date is collected row 1 shifts down to row 2. Row two still looks good but there is no longer a formula for the new rows inserted. To be clearer... I would like to have the following: =SUMIF(TotalSales!$B:$B,A1,TotalSales!$H:$H) in cell B1 =SUMIF(TotalSales!$B:$B,A2,TotalSales!$H:$H) in cell B2 =SUMIF(TotalSales!$B:$B,A3,TotalSales!$H:$H) in cell B3 How can I keep that formula in row one as I keep inserting new rows?

Combine Sum / Sumif And Subtotal www.mrexcel.com

I'm trying to put these two formulas together: =SUM(SUMIF(STATUS,{"Pending","New","Issued"},PREMIUM)) =SUBTOTAL(9,PREMIUM) I tried =SUBTOTAL(9,(SUM(SUMIF(STATUS,{"Pending","New","Issued"},PREMIUM)))) and it did not work. I also tried =SUMPRODUCT(SUBTOTAL(9,(OFFSET(PREMIUM,ROW(PREMIUM)-7,,1))*(STATUS={"Pending","New","Issued"})) this also did not work. I do not fully understand the SUMPRODUCT function. I found this formula online and substituted the values. It worked for the other spreadsheet which did not include a SUM on the SUMIF formula (i.e. SUMIF(STATUS,"=Pending",PREMIUM)

SUMIF Range Parameters Seem Completely Off In Sequence www.excelforum.com

How do I adapt code so that it creates a sumif formula for Values in Column E. my current code is: HTML Range("E65536").End(xlUp).Offset(2, 0).Select ActiveCell.FormulaR1C1 = "=SUMIF(E:" & Range("E65536").End(xlUp).Address & ",""<0"",E:" & Range("E65536").End(xlUp).Address & ")" In short, if a value in Column E is < 0, then sum the amount in Column E, and this should give a total of all the negative numbers in column E, but the range parameters seem completely off in my sequence.

Sumif For Dynamic Range ozgrid.com

I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time. I want the code for a dynamic range. VB: Range("E7").Select ActiveCell.FormulaR1C1 = _ "=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)" Range("E7").Select [Code] ....

Remove Reference From Copied Sheet www.ozgrid.com

How do I copy a sheet into a new workbook without the formulas referring to the old workbook? For example, formulas end up like this: = SUMIF('[BAS05-07to09.xls]IN'!$B$6:$B$62,101,'[BAS05-07to09.xls]IN'!$E$6:$E$62). I want them to come across as I originally had them, like this: =SUMIF(IN!$B$6:$B$62,101,IN!$E$6:$E$62). here are too many to do it manually each time.

Using SUMIF Function To Ignore Error Cells www.mrexcel.com

I need to get a subtotal of a range (eg: A1:A30), however some cells in this range have #N/A errors due to particular formulas they contain. I could go into these cells and add a formula which 'catches' these errors, but there are far too many given my current time constraints. Is there any way to use SUMIF to add the cells which do not contain errors and skip the ones that do? (ie: SUMIF($A$1:$A$30,""&"Error Parameter")).

SUMIF "#VALUE" If Looking In Another Closed Workbook www.xtremevbtalk.com

I have this formula in a Workbook in my worksheet BASE =SUMIF('D:Promexma[tablacv.xls]TABLA1'!$C$1:$C$65535,BASE!E543,'D:Promexma[tablacv.xls]TABLA1'!$G$1:$G$65535)*F543When tablacv.xls is closed it returns #VALUEIf I open the tablacv.xls then I get the calculated value... why? ... is there any way to correct this... When the tablacv.xls is open this is how my formula is displayed...=SUMIF([tablacv.xls]TABLA1!$C$1:$C$65535,BASE!E543,[tablacv.xls]TABLA1!$G$1:$G$65535)*F543TIA

Sumif: Sum A Range www.excelforum.com

I have come across this SUMIF formula which sum a range and ignoring errors produced by formulas in the range, I can't figure out how this works and need the expertise of the Excel experts here. =SUMIF(A2:A9,"<1E100"). What does "<1E100" represent and how its work

Copy A Formula Down And Total www.ozgrid.com

I have a sheet that in Columns U and V I have a formula. Below is part of my macro. What I need is a quick easy way to copy this down in the macro based on colunm S. When colunn S ends it should stop copying the formula down and total each colunm. Range("U2").Select ActiveCell.FormulaR1C1 = "=SUMIF(C[-6],RC[-2],C[-9])" Range("V2").Select ActiveCell.FormulaR1C1 = "=SUMIF(C[-7],RC[-3],C[-8])"

How To Use SUMIF On Consecutive Cells Matching Criteria www.excelforum.com

How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80) =IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"") More data: Column A is a group of dates, but not in order, they are slightly mixed up Column H is a monetary value.

Summing Up A Specific Criteria In Multiple Tabs www.mrexcel.com

I am trying to review a cell range for a specific criteria, and then sum up another cell range if the criteria matches. Here are the formulas I have typed in - there are two columns I am trying to calculate using the same formula, they are next to each other: =SUMIF('MASTER POINT SCHEDULE'!I2:I841,"0ACA101",'MASTER POINT SCHEDULE'!O2:O841) =SUMIF('MASTER POINT SCHEDULE'!J2:J841,"0ACA101",'MASTER POINT SCHEDULE'!P2:P841)

Sumif With Dynamic Column www.ozgrid.com

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.

Nesting A New Criteria In Sumif www.mrexcel.com

I have the following Formula: =SUMIF('Sheet2'!C:C;Sheet1!B3;'Sheet2'!E:E) In this way I got the sum of all items located in column E wich have the same value than in B3 in Sheet 1.... How could I change the formula in the way that only returns the Values in Sheet2 E:E when (If equals to B2 and C2 in Sheet 1 then Sumif) The value which I want to use to filter is in the same columns in both sheets

Sum Based On 2 Conditions www.ozgrid.com

I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime. Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)

SUMIF Based On Month? www.mrexcel.com

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

Using Cell Values As Partial Spreadsheet References In Formulas www.excelforum.com

I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.

How To Sum Up The Cells Which With (sumif) Formula www.excelforum.com

i am using "sumif" formula in cell C5 and C10, the next step is to add up these two cells (sum up the figures of "sumif"), then the answer will be +15+6=21, how to do with using single formula.

Can't Insert Formula To Cell Via VBA www.mrexcel.com

I can't insert this formula to cell via VBA. Code: Range("AB38").Formula = "=TAN((RADIANS(VLOOKUP(G38,TABLE_24))))*(Z38-AA38)"

Incorporate (InputBox) Result In Formula? www.mrexcel.com

I need to often enter a formula such as: =SUMIF(H1:H500,"=a",G1:G500) but, I need to change the variable 'a' to whatever letter I need. I wrote the following: y = InputBox("Enter letter") So, how do I incorporate the 'InputBox' result in the formula? If I use =SUMIF(H1:H500,""=x"",G1:G500) I just get 'x' as a result, not the letter I input.

How To Put String Formula In Cell Using VBA www.mrexcel.com

For whatever reason, I don't succeed to write a formula with a string to a cell using vba The char(10) is not accepted Code: LimitGainLong_.Formula = "Limit" & CAR(10) & "Profit: " & LimitProfitLongGain

Multiple Condition SUMIF: Sum The Values Between Q77:Q176 www.ozgrid.com

I want use the formula sumif...something like that! =SUMIF(I77:I176;AND(>F1092;<F1091);Q77:Q176). I want sum the values between q77:Q176 if the value in i77:i176 is >F1092 and <F1091 But the multiple criteriun don�t work in the sumif.

Sumif Formula: Three Ranges Add Together www.excelforum.com

The section I have highlighted in orange has three ranges added together how would I show this in a simplified formula? =sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$85:$GA$85)+sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$44:$G A$44)+sumif(DailySalesAnalysis!$C$1:$GA$1,A25,DailySalesAnalysis!$C$41:$GA$41)

Multi Criteria Sum Formula Very Slow www.ozgrid.com

I have previously been performing a multi level SUMIF of named ranges with a large data range. This works but its inefficient as it would be better to preselect a subset range before the SUMIF begins matching criteria. Basically, i'd like to select a range of rows which will parse into the SUMIF formula. I have solved this by referencing an external cell to create the range.

Sumif Formula Not Summing And Counting www.mrexcel.com

I am looking to effectively complete a sumif formula but not summing, counting. I thought a countif would be a simple solution but the countif is different i.e. SUMIF (range,criteria,[sum_range]) COUNTIF(range,criteria) I want to be able to put =countif(b1:b10,A1,c1:c10) as you would with a sumif but I am told i have put too many arguements so what shold i be using??

IF Formula Based Around A Value It Does Not Equal www.excelforum.com

Is it possible to do an IF formula based around a value it does not equal? eg I have one column, that is perforning a SUMIF providing that a certain cell is equal to a particular constant. =IF([cell]=[value],SUMIF([range],[criteria],[sum_range])) In the next column I want to do it the other way round so that: =IF([cell] DOESNOT=[value],SUMIF([range],[criteria],[sum_range])) Is it just a simple symbol??

Sumif But Only Last 6 Occurrences www.mrexcel.com

I have this sumif function, but this will sum about 50 occurrences of data in E329, how do i adapt formula to just sumif last 6 occurrences?? =SUMIF(E2:E328,E329,I2:I328)

SUMIF Function To A Particular Value www.excelforum.com

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:

Conditionally Summing Non-contiguous Cells www.mrexcel.com

Lets say I have values in A1, A3 and A5 and I want to to build either a sumif or an sum if array formula to conditionally sum them. How do I do this, assuming I don't want to use VBA or named ranges, i.e. create an array of noncontiguous cells and evalute & sum them.

Using VBA To Insert A Formula www.mrexcel.com

I am wanting to use VBA code to insert the formula =IF(B8="","",VLOOKUP(B8,Information!C4:D200,2,FALSE)) into cell B9 (it is merged to form cell B9:B13). I have the formula copied into cell M4 on sheet 3 and have tried to reference it but can't seem to do it. I am happy either using VBA to reference it or using a VBA code to insert the text into cell B9 How can i get the formula from sheet 3 into cell B9 in Sheet 1, or simply put the text in.

Error When Entering Formula Using VBA? www.mrexcel.com

I have a formula that I'm using which works and gives me the right value. Code: =COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4) However when I try and enter the exact same formula through VBA using, I get "Run-time error 1004: Application defined or object defined error". I don't see why the formula will work when I enter it directly into Excel but it won't enter the formula through VBA. Code: ActiveSheet.Range("H4").formula = "=COUNTIF(bommech[PO1 No],"")+COUNTIF(bomelec[Procurement Status],G4)"

Formula Or VBA To Do Sumifs On Multiple Columns www.excelforum.com

On the attached work book I have 2 sheets on the total minutes sheets I need to run either a VBA or sumif formula that looks at all the Init columns and if it finds that name then it sums the duration cells corresponding with that name. Picking log-Rev6.xlsm

VBA Code To Create SUMIF Function That Adds Comments? www.mrexcel.com

how to create a function in VBA that works exactly as SUMIF, except it will also add a comment in the cell that references the formula, where the comment would be a list of the individual cells being added? Basically the comment would read "2+4+5+6" or "2,4,5,6" or something along that.

Summing Cells That Are Multiple Of 15 www.mrexcel.com

I am working on a sum formula for a column and I only want to sum if the value is a multiple of 15 or likewise if the value divided by 15 is an integer. I know that I could use several sumif statements to accomplish this, but there isn't really a maximum value. Is there a simpler way to accomplish this? I am comfortable enough with VBA so that is definitely an option as well.

DSUM Formula Instead SUMIF Formula www.mrexcel.com

I come across a spreadsheet that is using DSUM formula, I did not understand why someone used DSUM instead of SUMIF formula, because using DSUM formula they had to use two extra cells for criteria, as seen in this example. I like to know is there any advantage of using DSUM instead of SUMIF in this example. The formule they used is =DSUM(J109:K113,2,$M$2:$M$3) and the criteria is G,D so they are adding all Debits in that column. I changed the formula to =SUMIF($J$110:$J$113,"D",$K$110:$K$113) that of course does not need $M$2:$M$3 range criteria. G 3458224.37 D 3401317.50 C 3401317.50 C 56906.87 D 56906.87

SUMIF Vs. VLOOKLUP www.mrexcel.com

I've had experience with both - but more recently using SUMIF formulas on large financial models (several spreadsheets in one workbook). I was wondering if there is a considerable difference in calculating/processing time using either one. For example, will my worksheets be less sluggish if I use vlookups vs. SUMIF formulas? As an example -- I have 15 worksheets, referencing two "data tabs" using SUMIF formulas, to draw specific data into them. I'm wondering if I use VLOOKUP formulas instead, if it will make a difference and help speed things along

Moving Range In SUMIF www.mrexcel.com

At the moment I have the below formula that retrieves a value from a separate sheet (the INDIRECT part of the formula determines the sheet to be looked at, as there are numerous and they are dependent upon what is displayed in two drop down lists in cells B3 & E3). The problem is that the ranges F6:F30000, AJ6:AJ30000 and AA6:AA30000 are not always consistently in the same column position each and every time I receive a refresh of the report. The column headings, however, are always in the same row. Obviously I don't want to have to manually update the formula every time I receive a refresh of the report, so any way that I can get the aforementioned ranges to become more dynamic, so that the correct value will be retrieved each time? I am thinking something like an INDEX/MATCH combo but I am struggling to apply that idea to the 'SUM RANGE' section of the SUMIF function. This is the formula I have so far: =SUMIF(INDIRECT("'"&$B$3&"'!F6:F30000"),$E$3,INDIRECT("'"&$B$3&"'!AJ6:AJ30000")) /SUMIF(INDIRECT("'"&$B$3&"'!F6:F30000"),$E$3,INDIRECT("'"&$B$3&"'!AA6:AA30000"))

Sumif Function Not Giving Correct Total www.mrexcel.com

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