Ifs Ands Sums

Feb 21, 2008

I have a list of tenants.
Column A is the building number
Column B is the Area in metres squared
Column C is the tenant name

What I want to do is sum the vacancy (in sqm) for each building.

Ie, look at column A and choose the rows relating to a particular building, whose number is in column D1

Then look just at those rows and select those tenants named as "vacant"

Then add the areas from column B of those vacant plots.

View 12 Replies


ADVERTISEMENT

Multiple If Ands

Jan 11, 2010

I am trying to track the amount of time something is open for. I would like to break this down into 30 min, 2 hour, 4hr 8 hr and greater than 24 etc... I am using this formula on a field that has total minutes. However with this formula most of them end up being older than 24 hours when they are actually not, it appears the 2 hr works. I think I am missing some logic.

=IF(AND(L2>=30,L230,L260,L2120,L2240,L2480,L2

View 9 Replies View Related

SUMPRODUCT Multiple Ands/ors (count Of How Many Rows)

Jan 26, 2009

I'm having trouble with SUMPRODUCT. I would like a count of how many rows where:

Column A = PP
and
Column B = QQ or RR or SS
and
Column C = TT or UU or VV

View 2 Replies View Related

Nesting Multiple IF/ANDs: Get Any Value In The Cell If Either Of The Conditions Are True

Apr 20, 2009

Trying the following syntax and I do not get any value in the cell if either of the conditions are true. Pretty sure I'm nesting the functions incorrectly. Maybe I should use Index/Match? =IF(AND(E24="SI",F24="SI"),"ER2","",)IF(AND(E24="sn",F24="sn"),"er2","")

View 3 Replies View Related

Sums

Feb 21, 2009

Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?

View 9 Replies View Related

Average Of Sums

Feb 17, 2010

In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.

View 2 Replies View Related

How To Do 2 Sums In One Cell

Dec 10, 2013

I have a number in a cell, lets say its 900. I want to multiply this number by 12 and then divide that by 52.

So a calculator I would simply type in 900 x 12 / 52 = 207.7

On excel I tried:

=sum(a1*12) /12

and it didn't work....

View 2 Replies View Related

Refresh All The Sums

Feb 12, 2009

I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.

I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.

I have been working on this spreadsheet for weeks and can't get past this part!

View 9 Replies View Related

Sums Starting Over After Zero

Feb 25, 2009

I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.

View 9 Replies View Related

Using Sums In Macros

Jul 26, 2006

I made a budget for my project and have to include a macro. I wanted to have the macro pop up a box for the user to input the expense they wanted summed for the year (student loan, car payment, etc) and then have the macro sum the payments from each month. I couldn't figure out how to do the pop up box either, so figured I could just do multiple macros (20 or so) but then ran into the added difficulty of needing to only use one cell out of every four.

I have columns for budgeted, actual, monthly varience and year-to- date varience and only wanted to sum the actual.) I have read the thread about summing every nth, but trying it without the macro didn't give me the same answer as adding each on a calculator. Also, trying to record a macro of just adding cells didn't work (I figured it probably wouldn't, but I tried anyways.)

View 9 Replies View Related

How Do I Put 2 Separate Sums Into 1 Cell?

Feb 2, 2009

I have a listing of numbers in different catagories such as the following:

Type A:
1

Type B:
2


Type C:
4

Total:
7

I want to be able to have a cell automatically calculate to say:
Type A:
"(1/7)"

Type B:
"(2/7)"

Type C:
"(4/7)"

with that exact formating.

View 8 Replies View Related

Insert Rows With Sums Using VBA?

Feb 12, 2013

create a macro that would insert three rows with the first row summing the values in the lower two rows in columns E to J. The third row will get a cell formatting of black fill. In addition I would like the three rows grouped with the sum row showing when the group is closed. When the macro is executed the rows will get inserted below the last row in the sheet that has a cell format fill of the color black.

There is data in rows below the last black filled row so the inserted rows will be between that data and the exiting black filled row.

Perhaps it would be useful to explain how the designer has set up this worksheet and how the users are utilizing it. After a group of rows is created or new groups added, to insert an additional row within an existing group the user selects the black filled row at the bottom of the group and inserts a new row. This would ensure the subtotal at the top row of the group captures the values in the new row because the sum formula includes the black filled row. The black filled row also serves as a separator for each group. How to improve the design and function but the creator and users are not so open.

View 1 Replies View Related

A Sum Formuala That Adds Up All Sums

Sep 4, 2008

Is there a formula or a function that adds up all of the sums in a row.

******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA14=ABCD14603 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Delete Rows Where Row SUMS To Zero

Feb 5, 2008

I have a spreadsheet with 14 columns assigned to different regions. I need to remove all of the rows that have the contents of the rows under these columns as £0.00. In psydocode, the requirement is:

If Columns C:O = £0.00
Then delete

It's important that it only deletes the row if all the entries through C to O are £0.00.

View 7 Replies View Related

SUMS Of (start:end) Arguments Not Working

Jan 14, 2014

I have a ('start:end') function that's not working. A simplified example is shown below in the screen shots.

I want a given cell in sheet1 to produce the sum of factors within a given column/row arrangements and equations from sheet3 and sheet4.

I've tried all the arguments I can think of in their proper places including the ' arguments before and after start:end but nothing seems to work. My resulting answer is usually #REF!

sheet1.png
sheet3.png
sheet4.png
worksheets.png

View 14 Replies View Related

Conditional Sums Using Multiple Criteria

Feb 2, 2009

I've been struggling to figure out why my Conditional Sum formulas using multiple criteria keep reporting zero. I am tracking resource loads for a project which is divided into 4 parts (Iterations). I am trying to sum the total resource allocations per Iteration. see attachment!

View 9 Replies View Related

Cumulative Sums A Formula Or Function

Oct 30, 2009

I have one cell that brings up a random number using RANDBETWEEN and I want to keep a running total in a separate cell, but I can't figure out a formula or function to do this.

View 6 Replies View Related

Excel Time Calculations Sums?

Nov 28, 2013

i have a tab on the bottom that is labeled "Man hours 2013" i need to pull the information from this sheet and create a new tab that will pull the property name, how many weeks of service, and the total man hours for each property. i will then be adding a column were i enter the monthly price for this contract divided by the number of weeks we serviced to figure out our percentage of gain/loss.

View 11 Replies View Related

Auto Increment Sums Incorrectly

Jan 16, 2014

If I enter 1 in a cell, 1.1 below, select both and drag down, I should get a vector: 1, 1.1, 1.2 etc.

However, in the most recent instance of seeing this problem, at 6.5 I get 6.50000000000001! 6.6 onwards is then correct, at 7.2 the value is again incorrect in the 14th decimal place, and the errors continue intermittently through the sequence.

Is there a fix for this? It's extremely time consuming to have to check each auto-incremented array like this.

View 4 Replies View Related

Sum Columns And Highlight Lowest Three Of Sums

Oct 27, 2009

..............

View 12 Replies View Related

Multiple Sums Depending On A Description

Dec 28, 2005

I really don't know how easy or difficult it might be, but here it is:

I have something like this (example) :

--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------

I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)

View 9 Replies View Related

Sums The Values In A Range Only To The Date

Dec 19, 2008

Need to write a formula which sums the values in a range only to the date we mentioned. For ease sample date & requirements are highlighted in attached spreadsheet.

View 3 Replies View Related

Pivot Chart - How To Show All Sums

Nov 6, 2013

I have a pivot table/chart that I need to show the sums as top 20 (or any other number) and All others. How can I do this.

I have an example file here: [URL] ...........

View 4 Replies View Related

Months And Quarters Sums Not Equal?

Jun 26, 2014

My list has dates. I am using this formula to determine the quarter for the date: ="Q"&INT((MONTH(S2)/4)+1).

As well I am using a formula to determine the month for the date: =E2-DAY(E2)+1.

In my book I have some sheets showing totals by month, in other I total up by quarter. I am experiencing a variance between the quarterly total by months and the quarterly total by quarter.

So I thought I'd try this formula to reconcile the two--but no success: =SUMPRODUCT((System=$B5)*(Created>=Lists!M$2)*(Created

View 5 Replies View Related

Compiling Large Sums Of Data

Oct 25, 2009

I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.

What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.

There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.

This sounds like a complex function to me but I am hoping that someone has a solution for me.

View 10 Replies View Related

Finding Sums Of Different Numbers Of Cells

Jun 21, 2006

I am building an inventory simulation and have run into a problem. What i want is, when i change a number in cell H4, i want excel to find the sum of C25 and the cells "H4" up. If H4 is 5 then i need the sum of C20:C25...if H4 is 10 i need C15:C25. Does anyone have any thoughts on how to do this? I have attached a sample sheet to make it more clear.

View 2 Replies View Related

Calculate Sums, Multiple Criteria

Sep 2, 2007

I have a worksheet of hockey player statistics that I need to tally the Career Totals and the Totals for One Specific Team. Since each player has played a different amount of years in both their career and by team I cannot use a simple Sum formula as each player will have a different amount of rows to calculate and the rows may not be congruous.

The statistics also use text "--" to specify when a player did not play and "0" for when they did play but no statistics were registered. When the totals are calculated, I need to know whether they were registered or simply didn't play (i.e retaining the "--" in the totals instead of "0" if they did not play in those specific years). One example is listed, while the rest need filling in... attached is a partial sample of the data

View 7 Replies View Related

Hide Rows Across All Sheets Where Row Sums To Zero

Jan 23, 2008

Is there a way to append this code to accomidate accross all sheets?

Sub HideRows()
ActiveSheet.Unprotect Password:="admin"
On Error Resume Next
With Range("e4:e34")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction. Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
ActiveSheet.Protect Password:="admin"
End With
End Sub

View 9 Replies View Related

Get The Collective Sums For Each Day Of The Week Using Pivot Table

May 29, 2009

I have raw data with date & days of the month .

From this raw data I want to get the collective sums for each day of the week using Pivot or any other means - I tried Pivot but propabaly due to lack of my understanding of the same the desired result was not recived

View 3 Replies View Related

Selective Sums: Trying To Total Different Colored Cells???

Sep 17, 2009

i have a "budget" worksheet and am trying to total different colored cells and want to know if this can be done. attach a sample.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved