SUMPRODUCT As SUMIF #DIV/0 Error

Jun 17, 2009

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.

SUMPRODUCT Or SUMIF?

Oct 21, 2008

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

Sumif Or Sumproduct?

Feb 26, 2009

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.

SumIf Or SumProduct

Aug 23, 2009

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!

Sumif & Sumproduct (combined Together)

Oct 29, 2008

i would to combined these together

can this be done

3 Conditions - SUMIF Or SUMPRODUCT Or...?

Jan 6, 2009

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.

SumIf/Sumproduct If Contains Detail

Mar 10, 2009

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)

A Sumif Of Sumproduct Question

Jul 9, 2009

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

Sumproduct Or Sumif Formula

Aug 24, 2009

a1=good
a2 = 100

b2 = 50

c1 = good
c2 = 100

would like to sum a2+b2+c2 only if a1,b1,c1 = good.

Sumif & Sumproduct Combination

Oct 9, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.

SumProduct / Sumif, Within 2 Times

Jan 20, 2010

From 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"?

Convert Sumif To Sumproduct

Dec 8, 2010

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)

More Than One Criteria In SUMIF Or SUMPRODUCT,

Jan 19, 2009

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.

Sumif? Sumproduct? If? Vlookup

Jan 30, 2009

I 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&current=untitled.jpg" target="_blank">http://i401.photobucket.com/albums/pp94/nmweir/untitled.jpg" border="0" alt="Photobucket">

http://i401.photobucket.com/albums/p...r/untitled.jpg

Sumif V Dsum V Sumproduct

May 2, 2007

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.

Sumproduct Or Sumif Date Range?

Jan 27, 2008

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 .

Sumproduct And SumIF Commands Merged

Dec 19, 2013

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

SUMPRODUCT / SUMIF With Named Range

Mar 26, 2007

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.

2 Criteria Sumif Or Sumproduct With Wildcards

Mar 27, 2009

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

Sumif/Sumproduct Across Columns & Rows

Jul 2, 2009

Is there a way to replicate this code, so I don't have to Do the With [CODE] .......

SUMPRODUCT INDIRECT & SUMIF Formula

Jul 12, 2006

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

SUMIF Or SUMPRODUCT For Multiple Criteria And A Negative?

Nov 12, 2011

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.

Combine SUMPRODUCT, SUMIF, COUNTIF Within Date Range

Jan 13, 2007

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

Excel 2007 :: Complex SumIf / Sumproduct Formula?

Oct 11, 2011

I am trying to do a dynamic sumifs that allows the user to use data validation lists to select 5 items to query. This is the easy part.

The hard part is that the amounts range to sum goes across 6 weeks and I want the answer to also take account of another validation input which specifies up to what week the range should be.

I have done a simply if statement in row 8 that flags weeks relevant to input cell B6.

How do I do a formula in say cell A16 that does the sumifs and acknowledges that the user has requested a dynamic range to sum?

Sheet1ABCDEFGHIJK1RegimePost 20052TypeBudget<<<<- Dropdowns3Prod AreaLoans4ChannelRetail5Pro/ReReactive6Week18-May-1178Flags ->1110009Regime 2TypeProd AreaChannelPro/Re04-May-1111-May-1118-May-1125-May-1101-Jun-1108-Jun-1110Post 2005Budget Loans Retail Reactive 36942041320736540011Post 2005Budget Cards Retail Reactive 38423444021726636212Pre 2005Actual Mortgages Tele Proactive 35739840536923740113Pre 2005Actual Loans Direct Proactive 3213872554552254621415Answer16Excel 2007

SUMIF Ignore Hidden Rows, Sumproduct/Offset Too Slow!

Jan 29, 2010

I'm using Excel 2003. The sumif function will not allow me to ignore hidden rows in my data set that have been autofiltered. I tried using the following formula and it worked:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(CopiedB!\$I\$2:\$I\$65000,ROW(CopiedB!\$I\$2:\$I\$65000)-ROW(CopiedB!\$I\$2),0,1)),--(CopiedB!\$A\$2:\$A\$65000=\$A22),CopiedB!\$I\$2:\$I\$65000)

The problem I am having is that the sumproduct and offset funtions used to ignore hidden rows are considered "volatile" and force a recalculation. Moreover, I have this fomula pasted 100+ times and this dramatically slows things down even when I turn autocalculate off. Is there another way to accomplish a sumif with a user defined custom function in VB? I really need to have the ability to do a sumif that ignores hidden rows and is not dramatically slow.

Excel 2007 :: Determining / Counting Number Of Values Used In SUMIF / SUMPRODUCT Function

Aug 2, 2014

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)

Sumproduct Gives #N/A! Error

Jan 27, 2007

I have searched and found a few postings for #NA errors

The formula I am using is SUMPRODUCT:

=(SUMPRODUCT(('On Premise Activity Tracker'!D19:D26="MAY")*('On Premise Activity Tracker'!H19:H38='On Premise Activity Tracker'!AG32)))

I am trying to count up the number of times 'On Premise Activity Tracker'!AG32 was used from 'On Premise Activity Tracker'!H19:H38 only if it was in the month of MAY.

This is a working document that is going to track information over a years time. Therefore, I have noticed that the formula only works when there are values in the cells. However, when I increase the range to the whole workbook I get the #N/A error because I do not have data in some of those cells.

Sumproduct #Value! Error Due To Text

Feb 26, 2007

I'm using this sumproduct and it works until a cell has text.
=SUMPRODUCT((\$C7:\$C16=\$C\$2)*(\$D7:F16))

I've tried =SUMPRODUCT(--(\$C7:\$C16=\$C\$2),(\$D7:F16)) but got the #Value! error because of the D7:F16 range. If I changed it to become F7:F16, then it works. But...my original formula adds all values giving me a YTD result. If I changed the range to be only F7:F16, then all I get is just that column's data. I have January to December's data across the columns from D to F. Any ideas on keeping my D to F range? I've tried Transpose but it didn't work either.

Sumproduct Loop Error #N/A

Mar 9, 2007

What is the trick for entering the following equation?

=SUMPRODUCT(LOOKUP(B3:F3,{0,0;"A",4;"B",3;"C",2;"D",1;"U",0}))/MAX(1,COUNTA(B3:F3))

When I enter this equation, it yields an #N/A error

Perhaps the { is being entered incorrectly ?

#Value! Error When Doing Sumproduct Of Two Ranges

Oct 2, 2007

I have a weird problem with some cells calculating (for example) =sumproduct(bundleNoNRP, bundleNoQTY). There are a number of cells with similar formulas calcutating with named ranges, and they all display #Value (a value of the wrong data type is used in this formula).

Both these ranges are set up via a VB program.
If i just seperately on the sheet do the sum of either of these ranges it works fine.

What could be going wrong? Is there some way of checking whats in the ranges? I cant see why the data type would be wrong if I can do sum calcs ok..Is there some rule which would mean the calculation wouldn't work anymore? This is part of a much larger VBA project so I hope I have provided enough information.