# Sumproduct Function

Nov 23, 2007

In this file I have a team in column A, in B are the relative number of games they have played, and in C the filed goal % for them in that game. The value I am trying to return the average field goal % for that team in there last 10 games. I thought the sumproduct would work but is not returning a value,

## Sumproduct With Contains (IN) Function

Oct 1, 2009

Ford
Chevrolet
Yukon
Chrysler
Ford Taurus
Fusion by Ford

these strings are single cell values. Each has a corresponding column with counts of how many times these exact phrases were typed in. I need to do a sumproduct function (i think) that will sum all counts on car MANUFACTURER. Thus i have my own list that contains (Ford, Chevrolet, Cadillac, BMW, Audi, etc).

I want to count any instance for these, so in the example above, the phrases Ford," "Ford Taurus", and "Fusion by Ford" would all be included.
Attached is sample spreadsheet.

## Sumproduct With An If Function

Jan 18, 2009

See attached file. this is excel 2003 and using sumproduct, but i cant get around with an if function.

## IF And Sumproduct Function Together

Dec 23, 2008

=IF((-('Gained Flat Table'!AB2="FTL")),(SUMPRODUCT(-('Gained Flat Table'!\$A\$2:\$A\$2774="UK"),-('Gained Flat Table'!\$E\$2:\$E\$2774="ROAD LOGISTICS DOMESTIC FTL"),'Gained Flat Table'!\$AA\$2:\$AA\$2774)),0)

I tried to use only sumproduct function, the formula is working.

But when I add IF function, I get result as '0'. I couldn't figure out the problem.

## SUMPRODUCT With OR Function

Feb 11, 2010

I've got a sumproduct formula, and saw from another post how to add in an OR function.
Its working but what I need it to do is to exclude if it meets the OR function:

=SUMPRODUCT((rABReseller="Non-AB Reseller")*(EPP_Rev_Strm="New Lic")*(EPP_PA="PA")*(ISNUMBER(MATCH(N10:N54,{"Unassigned","To Be Assigned"})))*EM_PP)

So in the above, if it is "Unassigned" or "To Be Assigned", it should be excluded from the calculation.

## Countif/Sumproduct Function?

Mar 9, 2009

I am trying to count number of staff working in any day without deleting the empty cells.(see attached sample sheet). I tried sumproduct as I have two criteria but it is giving me a "zero" result. I only want to count cells in range B3:B32 that does not have "#" sign if there is a staff name if there is a staff name id corresponding cell in Column A within A3:A32.

## Using SumProduct Worksheet Function In VBA

Feb 5, 2009

I'm having a hard time figuring out how to use the SumProduct worksheet function in VBA. The below worksheet formula works: =SUMPRODUCT((accProv=D2)*(accFYE=E2)*(accAcc))

- or -

=SUMPRODUCT(--(accProv=D2),--(accFYE=E2),(accAcc))

I tried the below variations in VBA, and I keep getting run-time error 13.

## How To Use Indirect Function Within Sumproduct

Dec 29, 2009

I have the following formula, which works; however I need to make it dynamic.

## Sum And Count Using SumProduct Function

Jun 9, 2009

*ABC53TypeCost\$Bought54Orange3Friday55Orange5Sunday56Apple4Friday57Orange5Friday58Orange4Sunday59Apple4Friday60Banana3Tuesday61Orange4Sunday Excel tables to the web >> Excel Jeanie HTML 4

Now, i want the Sum and Count of Orange which is bought on Friday

in D54 =8 and E54=2

## Adding Third Criteria To Sumproduct Function

Feb 5, 2014

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!\$D\$15:\$D\$8998,LEN(A480))=A480)*(MONTH(Sold!\$M\$15:\$M\$8998)=12)),"")

I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.

I tried to edit it myself with the function below but it didn't work.

=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!\$D\$15:\$D\$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!\$M\$15:\$M\$8998)=12)),"")

## How To Convert From COUNTIFS To SUMPRODUCT Function

Feb 25, 2014

convert this equation into sumproduct function..??

=COUNTIFS(\$I3,">="&\$AC\$3,\$I3,"<="&\$AC\$4)

## Making Complicated Sumproduct Into Function?

Jul 9, 2014

So I have a rather complicated IF, Countif, Sumproduct combo in a formula right now. As you can imagine this makes my worksheet run rather slowly. I was curious if there is any way to make this formula into a User Defined Function. The formula looks like this:

[CODE]
=ABS(\$BD15)),--(\$AX\$17:\$AX\$10000=\$AX15),\$BD\$17:\$BD\$10000))

[CODE]

I would not be surprised if this isn't possible but thought I would try. Another possible approach would be to make this into a macro and instead of the output being "Duplicate" or "Research" just have the "Duplicate" Cells Highlighted in Red and the "Research" Celss highlighted in yellow.

## Count Function Similar To Sumproduct?

Dec 12, 2011

Similar to SUMPRODUCT, do we have any function to count cells with diffrent critereas. Countifs does not work since the critereas are at different coulmn and rows.

## Add Sales Between Two Dates Without Using Sum Or Sumproduct Function

Jun 23, 2012

Query below

Date
Sales

1/7/2011
500

2/7/2011
600

[Code] ........

I was given the above table in an interview and was asked to find the sum between two dates without using sum and sumproduct.

## Keyword Search Function Using SUMPRODUCT

Oct 30, 2009

I am trying to set up a keyword search where someone can type a word in D8 and the number of books with that word in its title will be returned.

I have managed to get the following sum to work if I put the exact title in the search however I would like it to be possible for a partial search.

=SUMPRODUCT(--('Books List'!G2:G1166=D8))

## Countif Or Sumproduct Function (count In A Range)

May 6, 2009

I am trying to do a basic count in a range, however am a little brain fried and its not working. On attachment, column D is to list all times out of column A and B listing only unique entries (have tried to use Macro with advanced filter however not liking it being an extract). Column E is to be a count of how many times the break time in Column D appears.

## SUMPRODUCT Function With Date Calculation Criteria

Jun 11, 2009

I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.

One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.

## Array To Match Several Results Within SUMPRODUCT Function

Aug 25, 2008

I'm using a formula (with the SUMPRODUCT function) within which I need to match the month & year from an array with 3 "true" possibilities...

Meaning that if data (month & year) from the array matches any of the 3 months & year of a specific quarter (for example July 08, August 08, September 08), the result of that section should be "True".

The data to be matched (any of the 3 months of each quarter) is drawn from a table header (showing every month of the year) by using this "monthly" formula:

TEXT(H7;"mm.yyyy") where H7 holds "01.07.2008" (but displays "Jul. 08") and returns "07.2008".

I'm trying to extend it to something like:

TEXT({H\$7;I\$7;J\$7};"mm.yyyy") to match either July 2008 (H7), August 2008 (I7) or September 2008 (J7) for the 3rd quarter of 2008 but when I hit Enter, I get an error and H\$7 is highlighted ("ctrl + shift + enter" doesn't help).

Here's an excerpt of the formula that works well for other queries on my worksheet:

=SUMPRODUCT(--(TEXT('Current update analysis'!\$O\$2:OFFSET('Current update analysis'!\$O\$2;(COUNTA('Current update analysis'!\$B:\$B)+20);"mm.yyyy")=TEXT({H\$7;I\$7;J\$7};"mm.yyyy")); --('Current update analysis'!\$k\$2:OFFSET('Current update analysis'!\$k\$2;(COUNTA('Current update analysis'!\$B:\$B)+20)="ok"))

how I could/should structure the formula for it to work?

## Define A Range For SumProduct Function Based On An If Statement?

May 31, 2006

I want to calculate a weighted average but I need it to only calculate on the months to date. I have a data validation drop down on a title page that is toggled to the current month each time a report is needed to define what months have actuals. I bring in data for all the months but only the current months have actuals. I need to calculate the weighted average on ONLY the ACTUALS. How can I set the ranges for sum product based on the data validation list on the title page?

## Only Include Month Of January In Sumproduct Function On Another Page Using Array

Dec 27, 2013

I only want to include the Month of January in a sumproduct function on another page using a name manager array.

I was thinking of using

=SUMPRODUCT(INDEX(DATA,0,17),--((MONTH(INDEX(DATA,0,5)=1)*INDEX(DATA,0,17))

Where the dates are in the 6th column and include blank cells at the bottom and in the first row it says Date.

## Large SumProduct Array -- Original Title:Loop Function

Dec 19, 2007

could provide me a loop function in VBA for these calcultions.

Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1

These are the calculations for E1-G1.
The process must be repeated till E50-G50

So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2

## Changing Range Of Sumproduct Function Depending On Length Of Data Series

Feb 20, 2007

I have a small problem using the �sumproduct function� which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue � row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?

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

## Excel 2010 :: VBA Sheets Copy Function Works But Then Jumps To Beginning Of Function

Apr 8, 2014

I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:

[Code] ........

When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.

## Inserting An INDEX,MATCH Function Into A HYPERLINK Function Instead Of Cell Reference

Mar 20, 2009

Another interesting dilemma to solve. Using this formula:

## Function Returns Value To Cell As Single - Function Is Defined To Return Double

May 14, 2013

Function Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double

[Code]....

## Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function

Apr 13, 2007

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

## Sum & Sumproduct

Dec 18, 2008

I have the following formula's:

SUMPRODUCT((\$O\$9:\$O\$259="Active")+0)+SUMPRODUCT((\$O\$9:\$O\$259="Clearing Tmrw")+0) and

SUMPRODUCT(--(\$O\$9:\$O\$259="Active"),\$B\$9:\$B\$259)+SUMPRODUCT(--(\$O\$9:\$O\$259="Clearing Tmrw"),\$B\$9:\$B\$259) and

SUMPRODUCT(--(YEAR(\$A\$9:\$A\$259)=\$E\$4)*(\$O\$9:\$O\$259="Active"),\$B\$9:\$B\$259)+SUMPRODUCT(--(YEAR(\$A\$9:\$A\$259)=\$E\$4)*(\$O\$9:\$O\$259="Clearing Tmrw"),\$B\$9:\$B\$259)

My spreadsheet A1 to Q7 are where the headers and above formula's are and row 8 is hidden and has other formula's in (col C,M,N,O,P & Q)

The User then clicks the insert row macro which inserts a blank row (with row 8 formaules) into row 9.

However, the above formula's then change from 9 to 10 therefore, not picking up all the amounts.

Can anyone help me so that the formulas continue from row 9 ?

## Sumproduct..?

Jul 11, 2009

I have the following formula:

## SUMPRODUCT = #Value!

Mar 25, 2009

I have a spreadsheet in which I am using SUMPRODUCT. As you can see from the attachment, on the "Breakout by PIpeline" tab, Columns C, D & F are working perfect. However, Column B & E are duplicates of C & D - just wanting to pull the volume from the "Detail" tab instead of the dollars...and I keep getting the #VALUE! error.

I have tried formatting the Volume column differently - and even multipling the data times -1 and then pasting back in.....