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)

Thanks for any help.

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

- Three Variables To Look Up
- VBA -- SUMPRODUCT with VARIABLES and ORing
- Plotting three variables
- Three Variables
- System of equations in three variables in VBA
- Three Variables
- Three variables in a set of data
- How to create a three variable chart.
- Substitute three variables
- One graph three variable?
- Function with three variables
- Three variable lookup/index/match whatever
- Correct Syntax for VBA SUMPRODUCT with variables and named ranges
- Possibly Impossible Excel Multiple variable lookup
- SumProduct - multiple criteria and IF question
- How can i calculate a three variable if statement in excel
- Adding a range with three variables
- Sumproduct - four variables
- How can i calculate a three variable if statement in excel
- SUMPRODUCT evaluating variables based on if criteria
- Conditional Formatting In Vbasic For 10 Variables
- Assign Formula Values Based On Variables
- 3 Variable Data Tables?
- Create Array Of Variables And Sort

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.

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

graph where each axis is 0-100. Is there anyway to do that in Excel?

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?

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!

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?

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

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

Thanks!

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.

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:Countpay is dimmed as Long(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

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:Thanks.(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

It's driving me crazy and googling has not revealed an answer.

janie

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.

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

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!

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.

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)

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

- Craig

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