Free Microsoft Excel 2013 Quick Reference

sumproduct - three variables

I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. Any suggestions? The additional variable is in column G.

Thanks for any help.

SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)


Post your answer or comment

comments powered by Disqus
OK I have three variables:
The show, the network and the time.

I need a formula that will look at all three and then look at the array of data and to look up whether or not it is a :15 or a :30.

Enclosed is the sheet, let me know.

I seek your help with the following VBA use of SUMPRODUCT with Variables and OR-ing:

My Goal: Using VBA with SUMPRODUCT and EVALUATE and Variables, write code that will do the following (as an example):

If (Col A='PGV' or Col A='OAK') and (Col B='2008') and (Col C='Real') then add Col D

Worksheet Details:

---A-------B------C------D---
--PGV----2008---Real---400--
--OAK----2008---Real---200--
--BBB-----2008---Fake--100--
--PGV-----2007---Real---600--

The results of the above equation applied to the worksheet would be 600, record 1 and 2 would add, but records 3 and 4 would fail the conditional statement (record 3 because Col A = 'BBB', and record 4 because Col B = '2007').

Working VBA:

The following VBA code works perfectly:

Answer = Evaluate("SUMPRODUCT(--(($A$3:$A$1000=""PGV"")+($A$3:$A$1000=""OAK"")), --($B$3:$B$1000=""2008""), --($C$3:$C$1000=""Real""), --($D$3:$D$1000))")

PROBLEM VBA:

I need to make this code flexible to allow for multiple uses. If I replace the above code with variables, I have the following (this does not work):

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV"")+($A$3:$A$1000=""OAK""))"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

HINT #1 TO SOLUTION:

If I remove - from Piece1 - the second conditional statement, the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV""))"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

HINT #2 TO SOLUTION:

If I remove - from Piece1 - the second conditional statement, but keep the "+" operator (as an OR) the following code does work:

Dim Piece1 As String
Dim Piece2 As String
Dim Piece3 As String
Dim Piece4 As String

Piece1 = "--(($A$3:$A$1000=""PGV"")+1)"
Piece2 = "--($B$3:$B$1000=""2008"")"
Piece3 = "--($C$3:$C$1000=""Real"")"
Piece4 = "--($D$3:$D$1000))"

Answer = Evaluate("SUMPRODUCT(" & Piece1 & "," & Piece2 & "," & Piece3 & "," & Piece4 & ")")

IN SUMMARY:

I'm trying to use SUMPRODUCT with EVALUATE with VARIABLES and ORing.

I'm able to write working code with all of these pieces, except that the ORing statement is making for sleepless nights.

Can you help me with this?

Thank you

I have three variables (x,y,z) where x+y+z = 100. I need to plot them on a triangular
graph where each axis is 0-100. Is there anyway to do that in Excel?

I have one database that tracks four procedures done at 40 locations by 12
doctors. I want to create a spreadsheet that will count the number of
occurences taking placing of the procedures by location by doctor. In other
words Dr x performed 4 THR at Unit 10 and 3 THR at Unit 5, Dr Y performed 3
THR at Unit 1 and 3 THR at Unit 1. Any ideas on creating a three variable
spreadsheet?

Hi,

I need serious help for building system of equations in three variables in vba.

I guess that the system itself is not that hard, but my problem is the source of the values for the system.

In the attached file you can see that I have the data in a table which is dynamic.
I need the script to split the table in a range for each name. Then, use each 3 rows as a source for a system of equations and solve the system.

Please have a look at the file and tell me if something is not clear.

Appreciate your help!

I have one database that tracks four procedures done at 40 locations by 12
doctors. I want to create a spreadsheet that will count the number of
occurences taking placing of the procedures by location by doctor. In other
words Dr x performed 4 THR at Unit 10 and 3 THR at Unit 5, Dr Y performed 3
THR at Unit 1 and 3 THR at Unit 1. Any ideas on creating a three variable
spreadsheet?

Hi there

just looking for some help on how to present my data, my topic is the Barcham Sand Dunes of Peru. I have three variables that are Height, Width and Rate of Movement, I really do not have a clue how I could compose these variables all into one graph/diagram.

Any help would be appreicated

Thanks

I am trying to create a three variable chart, does anyone know how to
do it?, the three ones are percents, for instance, when a variable is
90%, the two other have to complete the 10 %left, and so on.

I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.

Hi I spent days on seeking a solution to present 3 variables. Here's an example:

I want to carry out a survey in Sept, Oct and Nov. I will asking 1000 random people in each survey what is their personal favourite--- iPhone, Nexus or Blackberry. Counts of each phone type is then divided into male and female.

Now we have three variables: gender, phone type and month. Is there any way I can present 3 variables on X, Y & Z axes and the value at their intersection as bubble size (or colour gradient)?? Theoretically it's possible but I can't find a way to do it in excel. Or anyone knows any software that can get the job done?

Thnks!!!!

I am trying to find out how to build a formula that will support three variables. I have a pricing schedule where we purchase pipe based on its length and condition. If the pipe is more than 15' and straight and round we are paying $300, If it is more than 15' and damaged we are paying $200, and if it is less than 15' we pay $100. I can figure out half of the equation by doing an IF function, but I can't figure out a formula that will calculate this for me each time. Please let me know if anyone knows a solution.

Thanks!

I have the following data

A B C D
1 1 1 A
1 1 2 B
1 2 1 C
1 2 2 D
2 1 1 E
2 2 1 F
2 2 1 G
2 2 1 H
3 1 1 I
3 2 1 J
3 3 1 K
3 3 2 L
3 3 3 MI have the first three columns (A, B, C) in two spreadsheets. I would like to key in on the three variables in those columns then return the corresponding data in the forth (D). So 2, 1, 1 would return E. The problem I'm having is that it takes exact matches in all three columns to return the correct answer. How can I do this? Any help would be greatly appreciated.

Hi,

I am having trouble getting the correct syntax for this VBA statement used to Countif based on named ranges and variables. Does anyone know what the correct syntax should be for this statement?


	VB:
	
(DateValue(TestForm.PayWeek.Value)) & ")*(PayB=" & TestForm.EmpCode.Value & "))") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Countpay is dimmed as Long
PayA and PayB are 2 dynamic named ranges
TestForm.PayWeek.Value and TestForm.EmpCode.Value are 2 user form fields.

I know these named ranges and variables are correct and contain data as the MATCH statement below works fine:


	VB:
	
(DateValue(TestForm.PayWeek.Value)) & TestForm.EmpCode.Value & """,PayA&PayB,0)") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks.
It's driving me crazy and googling has not revealed an answer.
janie

Hi,

I have been searching on this and other boards for a bit now, and can't find a match, so apologies if I have missed something obvious.

I have what I think might be an impossible request. I’ve been able to do things like standard deviation and the like with spreadsheets, but this has so far beaten me completely – I’ve looked at aspects like concatenate, sumproduct, sumif, arrays and other aspects, but it doesn’t seem to apply to what I’m trying to do

I’ve changed the actual details of the problem to make it more generic.

The Data

Consists of thousands of rows of individual events, all listed in a uniform method

– the vital figures are
Sales Amount 1
Sales Amount 2

For each record there are 15 columns of associated data
e.g.
Date
Day
Time
Location
Product
Class
and ten more

Some columns – like Class – have over thirty different possible values - so there are thousands of possible unique combinations.

The Problem

It is clear that in some cases the Sales Amount is affected by certain things – e.g. Friday afternoon is consistently higher than say Thursday morning.

However, because of both the large amount of both columns and variables, to test all the different variations manually would take possibly months.

What I am looking to do is for excel to run through all the possible variables
– firstly simply one at a time – date, time, location etc.
-Then all the possibilities of two combined variables – date&time, date&location, time&location etc
-Then three variables date&time&location, date&time&product, date&product&location
-Then four/five/six/seven etc

If I can get both a count of results (as e.g. less than 100 may not be significant), and also an average of the sales in that specific combination (to see deviation from average sale)

The Results
To list the specific results e.g.

Number of matching records

Average of sales amount 1
Average of sales amount 2
Then relevant variables
e.g. all Monday
then Tuesday etc
then onto
Monday

14.00

I hope this makes sort of sense – I’ve usually managed to stumble across the way to get Excel to do whatever I want before, but this has completely beaten me after longer than I care to admit.
Any advice deeply appreciated.

I have a spread sheet (18,000 rows) with 4 columns (Location, Date, Time and
Dept.). I want some specific results but think I need two steps.

First - in a helper column convert the time column to a single digit number
indicating workshift. The workshift times a 06:45:01 - 14:45:00; 14:45:01
- 22:45:00; 22:45:01 - 06:45:00 where 06:45:01 is first shift and so on.
This looks like it should be an IF statement but I'm not sure how to enter
the formula to show that if the time in the column is between the indicated
start & finish to show as 1, 2 or 3.

Second - once this helper column is created; in other sheets (one for each
shift), with column A duplicating the Dept numbers and columns B - M
indicating months, a SumProduct formula which identifies the number of
occurances for each Department by month.

Helpful, but not critical, would be to also identify the location (there are
2). I could duplicate the Depts on each sheet (there are only 46) if the
formula could have three variables (month, shift and location).

Thanks,

Ron R

I need to calculate a salary formala for the following and can only find a
formula for two variables. I have been successful getting the formula to
calcuate the values if the Advisor is equal to or above region, but I can't
get it to calculate if the Advisor is below region to enter 0.0%. I am
trying to complete a salary spreadsheet to put in the three region
percentages below (2.0, 1.0, 0.0). Here is the formula i am using...
=IF(B5>B6,B27,B26)
I need to be able to enter cell b25 if b5 is less than b6. Can anyone
assist with this, please?

Advisor 92.5% (cell b5)
Region 89.4% (cell b6)

Below Region 0.0% (cell b25)
Equal to Region 1.0% (cell b26)
Above Region 2.0% (cell b27)

Thank you for your help!

I have a spreadsheet that has site, job title and FTE. I want to add the FTE
by site and job title. I use for two variables the sumif function but can not
get it to work with three. For example,

Site Job Title FTE
Beaverton NP 1.0
Beaverton RN 0.8
Hillsboro NP 0.8
Hillsboro RN 1.0
etc.

This list is longer that this but I need to add the total of FTE for a
person who is a NP and works in Beaverton. Can someone help. Thanks.

I have Excel 2003.
--
Thank you.

I am trying to sort through some data with four variables. I thought a sumproduct would work, but it doesn't seem to be working. Here is the formula. Any suggestions?

SUMPRODUCT(--($L$9:$L$3038="Permanent"),--($E$9:$E$3038="No"),--($G$9:$G$3038="No"),--($C$9:$C$3038=R1),$H$9:$H$3038)

I need to calculate a salary formala for the following and can only find a
formula for two variables. I have been successful getting the formula to
calcuate the values if the Advisor is equal to or above region, but I can't
get it to calculate if the Advisor is below region to enter 0.0%. I am
trying to complete a salary spreadsheet to put in the three region
percentages below (2.0, 1.0, 0.0). Here is the formula i am using...
=IF(B5>B6,B27,B26)
I need to be able to enter cell b25 if b5 is less than b6. Can anyone
assist with this, please?

Advisor 92.5% (cell b5)
Region 89.4% (cell b6)

Below Region 0.0% (cell b25)
Equal to Region 1.0% (cell b26)
Above Region 2.0% (cell b27)

Thank you for your help!

Hi,

I have an issue in evaluating actual project spend for people working on the project.
The cost is evaluated by DAY RATE x FULL TIME EQUIVALENT (FTE).
The FTE figure is used for evaluation of cost and also for the evaluation of the additional effort used over normal hours worked.

My issue is that I need to record where resources work over 1 FTE (ie. over their allotted 7 hours a day) however when evaluating the cost, if greater than 1 is entered I need to evaluate it as 1

EG.
ColA -------------- ColB
DAY RATE---------- FTE
150 ---------------- 0.9
200 ---------------- 0.8
220 ---------------- 1.2

So I need SUMPRODUCT to evaluate as (150*.9)+(200*.8)+(220*1) {rather than 220*1.2}

I feel I need to use sum product as I also have VBA code in place where a user can add a further resource line (hence I can't simply use (150*.9)+(200*.8)+(220*1)

Please help

I want to make a spread sheet where the "shading" changes based on the text entered into the cell. ie I want to be able to enter yellow into a cell and have the cell shading change to yellow.

I have tried to use the conditional formating built into excel 2000, and it only has three variables available. This is perfect, exactly what I want to do, but I need at least 10+ colors. Most examples that i could find and alter use numbers, so I need instruction in how to do this with text.

Any help would be appreciated.

Wouldy

I have been fighting this problem for a couple of hours now and could use some real help.

I have three variables; Independent variables x & y, and dependent variable z.
x & y can take the values 1,2,3 and can equal each other
z can also take on values 1,2,3 but can never equal x or y.

I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit. Any one have any ideas?

=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))

Thank you in advance,
Brian

I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?

- Craig

Hi,

I'm not very good in arrays and I hope someone can help me out here.

I have a dynamic number of rows each with three colums of values. These varaibles I want to fill an array with but I don't know how.

Now I've just "concated" these three variables into a string and then I intend to split the string into rows by the third comma. (see below)

I think it's easier though to use an array and I really appreciate some assistance. Please tell me also the best way to sort the array. It will be sorted by var_Status which is an integer. (sort order: max positive to max minimum)


	VB:
	
PreString = PreString & var_StartWeek & ", " & var_Status & ", " & var_Totalh & ", " 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.