# Excel 2007 :: Calculate Total Value Depending On Certain Criteria

Jan 27, 2013

I use Windows Vista and Excel 2007.

So to cut to the chase :

On my spreadsheet, I have 6 main groups of columns, each one comprising 4 columns with their own automated calculations across the range.

Group 1: Cell H5, Cell I5, Cell J5, Cell K5
Group 2: Cell L5, Cell M5, Cell N5, Cell O5
Group 3: Cell P5, Cell Q5, Cell R5, Cell S5
Group 4: Cell T5, Cell U5, Cell V5, Cell W5
Group 5: Cell X5, Cell Y5, Cell Z5, Cell AA5
Group 6: Cell AB5, Cell AC5, Cell Ad5, Cell AE5

Then I have two columns following those: Col AG, Col AI

In each one of those two columns, I am trying to sum up a total value using The IF Function.

In Cell AG5 : the sum of the values of cells K5+O5+S5+W5+AA5+AE5,

ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AG3 (which is a symbol (wingding 3 for capital P), this occurs 3 times out of 6, sometimes twice only, and at random, so I need the sum of 3 cells (or 2) out of 6, based on the above criteria.

Same in Cell AI5: sum of the values of cells K5+O5+S5+W5+AA5+AE5

ONLY IF the values of Cells H5, L5, P5, T5,X5, AB5 is equal to AI3 (which is a symbol (wingding 3 for capital L), this again occurs 3 times out of 6, sometimes twice and again at random, so again I need the sum of 3 cells (or 2) out of 6, based on the above criteria.

Using the formula: =IF(T5=AI3,(W5),0) , I retrieved the value of cell W5 into cell AI5; and when I tried the formula :

=IF(T5=AG3,(W5),0) , in cell AG5, it returned the value 0 which is correct again, as T5 was equal to AI3 but NOT to AG3.

But I cannot work out how to add all of the three values relating to each symbol, independently in each column, as they each occur randomly.

This is the way it looks in the spreadsheet:

Cell H5 Cell I5 Cell J5 Cell K5/ Cell L5 Cell M5 Cell N5 Cell O5/
Symbol (for P) Text Text Value /Symbol (for L) Text Text Value

And so on 4 more times across.

I could of course add it all manually for each line but I would rather have a formula to do it for me as all the other calculations are automated and/or conditionally formatted.

## Macro Allow To Total The Data On The Total Sheet Depending On What Unit Number Is Selected

Apr 22, 2009

This may not be the best way to do this, but I don't know Macros or Pivot Tables.

I am looking for a way with formulas to do the following:
Within a workbook the 1st sheet is the data entry.
In another sheet that will total data from the data sheet is where I want to be able to total columns of data, depending on what is entered in one specific column:

Example:
Data Sheet, E2:E2999 is a unit number selcted by pull down tab entry.
G2:G2999 in the same sheet is where the data is.

Q: What formula would allow to total the data on the Total Sheet depending on what unit number is selected in column E on the Data Sheet and the data amount in column D from Data Sheet?

## Excel 2010 :: Add Code To A Macro To Calculate Something Depending On Months

Aug 22, 2012

I am looking for something to calculate values based on the past three months. I already have written a macro to retrieve all of the data and just need this as an add-on.

for example, lets say it is 5/31/2012. I have a row with dates going like

10/31/2010 11/31/2010 .........

I want the formula to calculate SUM(march+april+may row 68)/SUM(march+april+may row 59)

I have an input tab where I can select the month, say 5, and get the periods.

3/31/2012, 4/31/2012, 5/31/2012

if I changed the 5 to an 8 it would get 6/31/2012 7/31/2012 8/31/2012

I don't know if i need VBA for this but I am trying to have something to calculate this depending on the period selected in the input tab.

I am using excel 2010. I am thinking something with an offset function and maybe a loop? but still not sure.

## Excel 2007 :: Macro To Play Different WAV Files Depending On Value Of Certain Cells

Feb 19, 2014

I have built a darts scoreboard on Excel 2007, to track scores but also record statistics such as 3 dart average and -checkout success. As I don't know how to write macro's I've created it using formulae only - it's not fancy but does the job!

I have a number of .WAV files that call out the main scores - 60, 100, 140 and 180 and I'd like the scoreboard to play the relevant file when someone scores that amount. I've trawled through lots of forums but have been able to find one that does exactly what I need it to, and I don't know how to adjust the ones I've found.

This is how the scoreboard works:

There are 25 different sheets, one for each leg of the match that could be played. The sheets are named Leg1, Leg2, Leg3 etc.On each sheet, Player 1's scores are entered in cells J15 to J47, and Player 2's scores are entered into cells O15 to O47Once Player 1 or Player 2 wins the leg, I move on to the next sheet to play the next leg. All the scores entered remain in each sheet until the match has ended

This is what I need the macro to do:

When a score of anything other than 60, 100, 140 or 180 is entered, do nothingWhen a score of 60, 100, 140 or 180 is entered, play the relevant .WAV fileIf, for example, Player 1 scores 60 the '60' .WAV file plays. 60 will be left in J15, then Player 2 may score 100, which will be entered into O15 and should trigger the '100' .WAV file.Note that either Player 1 or Player 2 can start the leg, so the order scores are entered between column J and column O can differ.

## Excel Macro Or VBA To Calculate Desired Profit Margin Depending On Cost

Sep 12, 2013

I need to develope a macro for Excel to calculate desired profit margin depending on our cost of each item. Here's the scenario.

A1: landing cost
If 0

## How To Calculate Total Commission In Excel

Apr 1, 2014

How do you calculate the total commission of a particular person on a consecutive months ?

Attached is the excel file for reference : Total Commsn.xls‎

## Excel 2007 :: Show Total From Different Worksheet

Oct 13, 2013

I'm fairly new to Excel (2007).

I have used a simple COUNTIF formula (=COUNTIF(C7:C207,"Name")) on sheet 1 to give me the total I want.

What I now want to do, is display this total on a different sheet in the same workbook (Sheet 3) without having to take all the data from Sheet 1 over to Sheet 3.

## Excel 2007 :: Last Twelve Months Total Rolling

May 6, 2010

I am trying to add the total values from twelve months sales together, then when i add the next months sales value (month13) it is added to the twelve months total but the first months value is deducted from the total. So basically it is a rolling twelve months total figure that both adds the latest months value and deducts the first months value automatically. i am not sure if this is possible in excel 2007.

## Excel 2007 :: SumIf (total Value) For Supplier Codes

Feb 6, 2012

Column O is a list of unique 1000 supplier codes

Column G is a list of 75000 imports (dollar amounts), each row has a supplier code.

I want to have a formula in Column P to sum the rows where the suppier code equals the supplier in column O.

i thought this was easier in 2007.

## Excel 2007 :: Rolling Total For 2 Adjacent Columns

Jul 10, 2014

I use Excel 2007.

I have a data set that tracks ingredients in chemical products. For example sake the chemical product is a mixed color, and the calculated ingredient is a primary color.

I need to calculate the quantity of two primary colors based on their percentage of the mixture.

Column A contains the mixed color names. Columns B and C contain the percentage of the primary in each mix.

I began tracking this data in January 2009. Each month I add 3 columns to the worksheet. The 1st column contains the monthly mixed color quantity used. The 2nd and 3rd columns contain the calculation of the two primary colors.

I need the formula to calculate the 12 month rolling total for each primary color. (For July 2014 I need to calculate July 2013 through June 2014)

A
B
C
E
F
G
H
I

[Code]...

## Excel 2007 :: Conditional Formatting - Assign Each Prg Its Own Color On Total Tab

May 25, 2012

I am trying to create conditional formatting in Excel 2007 that is beyond my abilities.

I have 6 rows. The first row is a sum of all the others. Example here:

prg 1 prg 2 prg 3 prg 4 prg 5
Total
4 1 2
10 10

I would like to assign each prg its own color on the total tab, and if it spans multi programs a separate color all together.

## EXCEL 2007 :: How To Make Percentage Of Running Total In Pivot Tables

Jan 15, 2014

how to make a % of running total in pivot tables in excel 2007. the running total in is only available not the "%".

## Excel 2007 :: Calculate Hours From Time?

Jul 20, 2012

I want to calulate time by decimal the problem is anything after 1 am wont work

example start at 12:25 finsh at 1:45 time should show up as 1.33

using excel 2007

## Create A Spreadsheet That Will Calculate Total Money Spent And Total Savings?

Mar 5, 2014

I need to set up an easy to use spread sheet for my office. It needs to be able to calculate the running total spent of fuel, as well as include any discounts we get and then calculate our total savings.So basically, total spent and total saved.

## Excel 2007 :: Calculate 2 UserForm TextBoxes And Display Answer In The 3rd TextBox?

Jan 8, 2013

I have a UserForm that Dead Head Miles will be entered into one text box. The Trip Miles will be entered into the second text box I would like for the user form to show the answer after the first entries are made something like this:

2) Trip Miles 500
----times---\$2.00
3)Projected Rate \$1040 --show this after the DH and Trip is entered and Multiplied by \$2.00

## Excel 2007 :: Copying Word Pages To Workbook To Calculate Numbers By Using Formulas

Apr 10, 2013

I am working on word documents that has financial numbers on them. I am copying word pages to excel to calculate the numbers by using formulas and etc. I will cut to the chase; is there a way or a macro to insert into an excel template workbook(.xltx) when we open the template there will be a command button to select the word document (which we want to copy its pages) to excel sheets. When a new page begins a new sheet will be created and the page will be pasted on the new sheet.

It is not important if it has to have a command button to select the word document it is ok to run the macro and select the word document and it does not has to populate sheets according to length of word document (I can create lost of free sheets on the template)

The main idea is to gain the time I spend on copying the word document to excel sheets. Some documents can be very long (100 pages). I am using MS Office 2007.

I triend to export data but excel does not allow me to select word documents.

## Excel 2007 :: Multiple Value With Criteria

Oct 6, 2013

I have use excel 2007. In our sheet i have in cell "B3" product name i.e. "abc" , "xyz" etc. That is called sales of goods.

In cell "C3" i have number of quantity
If "B3" is "abc" then only calculate below :

1st option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=15 & cell "c3" is<19 then message shown as "one mobile free"

2nd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=20 & cell "c3" is<39 then message shown as "two mobile free"

3rd option : If product name is "abc" then only calculate (which is cell "B3") & cell "c3" is >=40 then message shown as "three mobile free"

In view of the above which formula applicable.

## Excel 2007 :: VBA Message Box With Criteria

Dec 13, 2013

I have a spreadsheet that monitors employees rest periods between shifts across a number of weeks I've started writing a very basic piece of code so if the employee has less than 12 hours rest, a message box will appear with a warning:

So far this is what I've done:

Sub RestHours()
If Range("I7") < #12:00:00 PM# Then
MsgBox "Candidate has had less than 12 hours rest since last shift worked. Please find another candidate to work this shift", vbOKOnly, "Unable to Use Candidate!"

Now this does work if I run the macro and then enter the data into cell I7, however I'm now stumped - How can I make it so that this message box pops up automatically when the data is entered into the cell without me have to go in and run the macro?

I also need the macro to run all the way down the I column (I7:I100) and across other columns which will eventually contain the same data i.e. M7:M100, Q7:Q100, all the way across to AT7:AT100?

## Excel 2007 :: VBA With If Search OR Criteria Copy

Apr 7, 2014

VBA to copy entire row in all worksheets in file based on criteria.

step1 Copy tab has criteria in cell c1

step2 search if this c1 value in each tab in column e or g or h

step3 check if column i is yes

then if criteria meet, copy entire row to copy sheet

I came across some similar posts in other forums which can do partial work but not entirely .

Note ; number of rows, columns and tabs varies in file

I am using excel 2007.

## Excel 2007 :: Extracting Criteria Into Another Worksheet?

Feb 15, 2012

Excel 2007. Is there a way to extract information from cells and rows that constantly move? These rows need to be able to cut & paste, copy & Paste, insert and delete. I have the formula I need to display the data, but with the cutting and pasting etc, my formulas get all messed up.

I have the excel sheet on Google docs. It displays some cells wrong because it does not recognize certain formulas.

[URL]

On the second worksheet, I want the information from columns A, H, M, N, and O to be extracted at a certain time each evening and sent to a worksheet, without sending duplicates. These rows will constantly move which is why my current setup, which is just to display the information, will not work... We decided to try to extract it into a different worksheet or workbook if possible.

## Excel 2007 :: Populating Data Based On Two Criteria?

Mar 28, 2014

I'm trying to populate a table based on two different criteria in a another table. On is based on a date and the other the number of a unit. I'm trying to get the data in sheets Week 1 and Week 2 into the format in sheet Example. Is there a way to create a series of functions, filters, etc that I could use to create a macro to do multiple sheets or at least a whole sheet at once? I've been trying to think through it with my limited knowledge of filters and functions

Here is the file with an example and I'm using Excel 2007: Example.xls

## Excel 2007 :: Find Out Maximum Date With Two Criteria

Aug 9, 2013

In excel 2007 i have three sheets.

In sheet1 : Cell : "D3" : I have find out & show maximum date with two criteria (i.e. code & series) from all sheets.

Also in cell : "E3 : I have find out maximum date with two criteria from particular one sheet only.

I have mentioned comments in attach file.

In sheet name 1112 & 1213 : I have a lot of data approx 40000.

Column A : Invoice no
Column B : Invoice date
Column C : Code
Column D : oano
Column E : Name
Column F : city
Column G : distric
Column H : Series

In yellow highlighted cell i required formula.

## Excel 2007 :: SUMIFS - How To Specify Criteria That Looks At Type Of Formula

Jul 31, 2012

I'm trying to sum over a range (column A) based on values in another range (column B). Column B's cell values are calculated with a simple numeric formula that is either a subtraction or an addition of two cell references. I want to sum all the column A cells for which the corresponding column B cells are determined by a subtraction. Then separately, sum the cells for which the corresponding column B cells hold an addition. It seems that SUMIFS is the function to use, but how do I specify the sum criteria as "a subtraction" or "an addition"?

Windows 7, Excel 2007

## Excel 2007 :: Using Sumproduct (or Alternative) For Hundreds Of Criteria?

Feb 13, 2013

I have a some data where I need to sum some data based on date range, and dept id's. But the dept ids are parts of various groups. For ex, one group covers about 300+ dept id's. Some of them have ranges (e.g. dept 1000 - 1050) but for whatever reason some dept id's that belong to other groups sometimes fall into the same ranges.

My first hope was one big range, but the overlap issue nixed that. As I mentioned there are some ranges so maybe 100 of them could be grouped into a range but I'd still have 200+ independent dept id's.

I read for 2003 the max criteria for sumproduct is 30 and for 2007+ it's 255. So maybe 2007 might cut it but this doesn't seem like the right way.

Btw, I'm doing this in VBA but I mention sumproduct because I've used it a few times within code via evaluate. Otherwise do I just have a massive string in vba (If deptid = #### or deptid = #### or deptid = #### etc)?

Again, it seems like there should be a better way. I thought about making a master list of all the dept's and sumproduct each one individually and then sum from the ones that fall under group but even then I have to identify the deptids I need for a particular group.

## Excel 2007 :: Returning Values Based On Criteria?

May 17, 2014

I'm using Excel 2007

I have the below data in sheet 1

In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD

DEB/CRED
ACC1
ACC2

[code]....

## Separation Of Results Depending On + / - Total

Jun 20, 2014

I have a grand net income and net loss rows and underneath those are costs which are like the breakouts of the income and loss. I need a function that spits out a separate total income (meaning sum of all the positive numbers of those breakouts) and separate total loss( meaning the sum of all the negative numbers within those breakouts).

In addition, I have a separate table (on my spreadsheet it starts on cell B13 and that table has the total for each of the breakouts. I want to have a function on that table too that when the number is negative, the number will appear on the Net Loss column. If it's positive, then it appears on the grand income column.

## Excel 2007 :: Subtotals With HIDDEN Rows AND Columns Given Some Criteria

Aug 8, 2014

I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.

GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B\$2:B\$5,ROW(B\$2:B\$5)-MIN(ROW(B\$2:B\$5)),,1))*(B\$2:B\$5="Pass"))

BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET(\$B2:\$F2,,COLUMN(\$B2:\$F2)-MIN(COLUMN(\$B2:\$F2)),1))*(\$B2:\$F2="Pass"))

## Excel 2007 :: Search And Return Result With 2 Criteria In Different Sheet?

Nov 7, 2013

I use the following formula in excel 2007 (and it's working perfectly in 2007) but this formula does not work if I work with the same file in excel 2003 ...w why and what I have to adjust?

(I'm working with the dutch excel version so it might be that ";" must be ",")

## Cell Populate Verbiage Depending On The Total

Feb 27, 2008

I am trying to have a cell populate verbiage depending on the total % of the cell to it's left. I have created this formula but it's not working.

=IF(F25=100,"Meets Expectations")+IF(F25>100,"Exceeds Expectations")+IF(F25

## Excel 2007 :: Looping Through Text Strings And Copying Based On Criteria

Sep 17, 2012

I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)

File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.

File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work

Code:
Option Explicit
Sub get_data_from_2()

Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range

[Code] .......

the bolded part gives me an error.