Summing Totals Through IF Function...

Nov 10, 2008

What I am trying to do is have the formula look at the order statuses in column E and total the dollar amounts attached to the status of those orders and sum them up. So, for example, if I wanted to see everything "On Hold" I'd enter this formula I am trying to figure out, and it would should me the sum for everything on hold.

Right now, I've been working with these formulas, but noticed there would be a problem (most likely) with Excel not looking at each occurence of the word "On Hold"; it would more than likely just stop at the first one:

=IF(E2:E51="On Hold,I2,"0") or =IF(E2:E51="On Hold,I2:I51,"0")

and

=IF(VLOOKUP("On Hold",E2:E51,1,FALSE)="On Hold",I2,"0")

But neither work and usually get #VALUE!

I found this does work:

=IF(E2="On Hold",I2,"0"), but then I'd have to do:

=IF(E2="On Hold",I2,"0")+IF(E3="On Hold",I3,"0")+etc.....

But the end result would be a function for only one cell that contains like over a hundred calculations. I'd be time consuming and a pain to maintain.

View 2 Replies


ADVERTISEMENT

Deleting Duplicates And Summing Totals

Jan 27, 2014

sample3.xlsx

I'm still having issues with this workbook. I cannot use a pivot table to fix it, I don't understand them and it confuses me greatly..... so I'm at the mercy of either a formula or macro. I need to combine the duplicate part numbers (a), total the quantities, average the price (d), and total the amount of the part (e). I'm having a very difficult time with it.

(I have a sample attached that is file sample 2, and the entire spreadsheet attached- sample 3)

View 4 Replies View Related

Summing Totals Of Filter Results

Feb 8, 2007

A friend of mine asked if there is a formula that will add the "Y"'s and "N"'s based off of a filter per certain dates. Below is a brief example.

2/18/2009Y
8/15/2006N
2/18/2009Y
8/15/2006N
4/6/2005 N
4/6/2005 N


We want to be able to sort by dates so regardless of which date we pick, below the Y's and N's will be the total for the Y's and N's that are visible. If sorted by April, it will show 2 N's and 0 Y's, and so on. Is there any formula out there that can do this.

View 3 Replies View Related

Summing Daily Weekly & Monthly Totals

Feb 26, 2009

I assume there's a database (or pivot table?) solution for my task, which is to detect the days, weeks and months within a very long table, and obtain the totals of the data therein.

See attached worksheet.

View 8 Replies View Related

Merging Columns / Deleting Rows And Summing Totals

Mar 22, 2012

My data in excel looks like this:

A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 2
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday 12:00 P.M. 1
3/22/2012 0:00Thursday Not Requested 2
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 3
3/22/2012 0:00Thursday 12:00 P.M. 4
3/22/2012 0:00Thursday Not Requested 1
3/22/2012 0:00Thursday 12:00 P.M. 2
3/23/2012 0:00Friday Not Requested 2
3/23/2012 0:00Friday Not Requested 5

What I'd like to be able to do is if Column A,B, and C are the same to sum column D. So, for example, I would like the data above to look like this:

A B C D
Visit_Date Day_of_the_VisitHousing_Tour_TimeNumber_in_Party
3/22/2012 0:00Thursday 12:00 P.M. 7
3/22/2012 0:00Thursday Not Requested 3
3/22/2012 0:00Thursday 12:00 P.M. 12
3/23/2012 0:00Friday Not Requested 7

View 2 Replies View Related

Using If Function To Sum Totals

Jan 2, 2014

I've been trying different combinations of "If" statements to get a sum for the total items in house or total items at outside vend.

I need something that will be able to differentiate from parts in house to parts outside vend and put the total sums into M2 and M4

Please see the attached sample : Sample Qty.xlsx‎

View 2 Replies View Related

Function That Takes A Value Froma Cell And Then Totals The Row Up To That Point

Oct 6, 2009

I have a very long if function that takes a value froma cell and then totals the row up to that point.... Is it possible to make this shorter? I have tried to put a sum function in there but that also comes with the nested functions have been exceeded error message...

=IF($S$8=12,P11+O11+N11+M11+L11+K11+J11+I11+H11+G11+F11+E11,IF($S$8=11,O11+N11+M11+L11+K11+J11+I11+H 11+G11+F11+E11,IF($S$8=10,N11+M11+L11+K11+J11+I11+H11+G11+F11+E11,IF($S$8=9,M11+L11+K11+J11+I11+H11+ G11+F11+E11,IF($S$8=8,L11+K11+J11+I11+H11+G11+F11+E11,IF($S$8=7,K11+J11+I11+H11+G11+F11+E11,IF($S$8= 6,J11+I11+H11+G11+F11+E11,IF($S$8=5,I11+H11+G11+F11+E1,))))))))

This one stops short, as it should continue down to $s$8=1.

View 2 Replies View Related

Summing In A VLOOKUP Function

Dec 17, 2008

I have a vlookup function that is looking up a range of cells. I would like it to look in the range and then sum up all of the values that meet the criteria. At the moment it will only bring back the value of the first match it finds in the range, I would like it to bring back the sum of all the values it finds in the range, is this possible? Example:

Vlookup(Jan-08,$a3:$c$7,column 4 values, 0), if Jan-08 is listed in the range more than once, I would like it to add up all the values in column 4 that match.

View 4 Replies View Related

Summing Using Index Function

Jul 9, 2009

I am trying to sum values on sheet1 in column CR using the index function, but it comes up as #ref!

see formula below

=INDEX(Sheet1!1593:1609,96)

View 9 Replies View Related

Countifs Function Not Summing Correctly

Jul 13, 2012

I have yet run into another road block, which is stated below.

=SUM(COUNTIFS(AF20:AF111,{"*CoSale**","*Reo**"},AL20:AL111,{"Seller - Core","Seller - Non Core"}))

I can not get that formula to sum correctly. I can get them to sum if I seperate, but not with the countifs formula above. I am stuck at this point. So, any insight would be great. It is supposed to add up to 13, but I am getting a sum of 7.

REO-Svcr
Seller - Core
CoSale-Pri
Seller - Core

[Code]....

View 3 Replies View Related

User Defined Function For Summing Multiple Criteria

Aug 5, 2008

I am in need of creating a User Defined Function in Excel that will sum numeric values based on two text criteria. I have a large set of data in an Excel worksheet that includes a column for " Market Type" and a column for "Location ID". I would like to develop a User Defined Function that allows a new or inexperienced user of the template to use the User Defined Function to select first a "Market Type" then select a "Location ID" and get the sum of the amount those values represent. A SUMPRODUCT function in Excel works for this, but can be awkward or intimidating for a new or inexperienced user. For that reason, I am specifically looking for a User Defined Function that will simply the formula for them while at the same time maintaining the degree of accuracy I need.

View 9 Replies View Related

Add Totals By Day

Oct 9, 2008

Here's what I have...

Column A Column G
date $ Amount

my data example...

10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/8/2008 $8.00
10/8/2008 $25.00
10/9/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are on the same day and then look in column G and add all of those $ amounts.

So the result would be...

10/5/2008 $30.00
10/8/2008 $45.00
10/9/2008 $75.00

View 4 Replies View Related

Sub Totals Won't Add Up

Jun 5, 2014

Here is what I have:

cell b6 =SUM(B3:B4)
cell b8 =C8*B6
cell b20 =SUM(B11:B18)
cell b29 =SUM(B22:B27)

I want to add all these cells together for the total. I used the formula =SUM(B6+B8+B20) and it works fine. However, when I try =SUM(B6+B8+B20+B29) I end up with 0.00 in cell B31

View 2 Replies View Related

Totals And Listing If Not 0

Nov 22, 2012

I'm tracking 50 food items (in column A) for 40 homes (columns B-AO) where each home gets none or varying quantities for each food item (ex: apples 7 for home B, 0 for homes C-L, 19 for home M, etc.). I can get grand totals for each item for all homes, but need to also list how many for each home receiving apples. Ex: 4 homes get apples (4, 7, 19, 1 respectively). SO I want my total sheet to have a column for each food item with the grand total "31" and the second column to show 4 + 7 + 19 + 1. The quantities change frequently so I need a formula I can use for each food item. Also I don't want "zeroes" included. What formula would I use to do this?

View 3 Replies View Related

Add Totals By Week

Oct 9, 2008

Does Excel know week numbers? e.g. Week 40 is Sunday 10/5/2008 to Saturday 10/11/2008. Here's what I have...

Column A Column G
date $ Amount

my data example...

10/5/2008 $10.00
10/5/2008 $20.00
10/8/2008 $12.00
10/11/2008 $8.00
10/12/2008 $25.00
10/13/2008 $75.00

What I want the formula to do is look in Column A find all of the dates that are in week # whatever and then look in column G and add all of those $ amounts. So the result would be...

Week 40 $50.00
Week 41 $100.00

View 3 Replies View Related

Column Totals

Jul 9, 2009

I need help to total a column but in four cells the values are as a result of conditional formatting and the total ignores those cells. Is this difficult or am I a bit slow? This is in 2003.

View 6 Replies View Related

Cumulative Totals

Oct 20, 2009

This problem has come at the end of a big exercise whereby I've managed to construct a spreadsheet that automates three payment processes based on various VLookups to another file. However, I've come to a stage where I need to pre-empt a payment allocation based on cumulative totals.

I'll try and explain clearly below but I've also attached a spreadsheet showing the intended result (along with a copy without the output so that someone can add in the formula - again, if it exists.

So, this all centres around a code allocated to our clients and a declaration that they complete. Say client code "Apple1" (col E) sends in instructions to pay on a position of "650,000" (col F). Within our existing spreadsheet we have an available position of "800,000" which can be seen by totalling column G for client code "Apple 1".

So that's the first requirement - for all rows on my existing spreadsheet for client code "Apple1", I want to add the cumulative total of column G ("800,000") I'd like this captured in column H, as per my example.

We can then make payment based on their declaration, up to their total available amount. However, we need to pay them in stages according to our existing allocations (col G). Therefore, taking the first example, their declaration show's an available position of 650,000, their total position is 800,000 and the available nominal amount for that row (col G) is 200,000 - so they can be paid on that full amount therefore, 200,000 should be shown in cell I3.

So now, they've got 450,000 left to be paid and cell G4 again shows a position of 200,000, so once again, cell I4 should show 200,000.

The client "Apple1" now has a payment amount remaining of 250,000 remaining but in this instance, cell G5 is for 300,000 - so because the remainder of their available payment is less than the amount in cell G5, we should post the available payment amount in that field - 250000.

Therefore, if you add up all of column I for client "Apple1" it comes to 650,000 - the amount we have on our declaration - even though the total of their available position is 800,000.

I've included another client in the mix "Sauce2" who should show 200,000 and 0 respectively in cells I6 and I7.

View 14 Replies View Related

Totaling Sub Totals

Apr 20, 2009

Let me first explain what my worksheet looks like.

View 2 Replies View Related

Tallying Totals Of Ten

Jan 11, 2008

I have a column of wickets (M). M5 and M6 are the number of wickets for one match, M7 and M8 for the next, and so on. Two wicket entries for each match.

I needed a formula to count the amount of times the combined wicket total for a match is greater than 10. Initially there were only a few M values, so I used this:

IF(M5+M6>=10,1,0)+IF(M7+M8>=10,1,0)+... and so on. Now I want to expand it to have more M values and this formula would become huge.

View 11 Replies View Related

Extracting Just The Totals?

Nov 29, 2011

I have a file tens of thousands of lines long. This has a bunch of store numbers along with the amount owing to them.

Now this is presented like this

Str 50 $10
Str 50 $20
Str 50 $15
Str 60 ... etc etc

I just need the totals for each store, which I will then copy into my billing file. The person who sent me this original has already subtotaled it, and each site has a plus sign next to it. When I click the plus sign I can see the breakdown (ie store 50 ten times, store 60 twelve times etc) but otherwise the store totals are given on 'adjacent' rows. The problem is these rows arent actually adjacent, the other rows are just hidden. So when I try to copy the store totals all the other junk gets copied as well.

How do I copy just the store number and totals?

View 6 Replies View Related

Sum Totals By Date

Oct 31, 2006

I would like to get the total in column B but its not giving me the correct total. So I would half to use a helper column in column D to retrieve correct results. Is there anyway I could have an all in one formula for this. I would prefer not to use the helper column. The correct result is in cell D23.

View 9 Replies View Related

Having Different Totals From Different Options

May 4, 2007

I would like to know how do I go about adding the following:

This is a test it counts out of 1250 points

But there are 3 fields which can be omitted from the grand total of 1250
Some students might not have all three fields. The fields totals are 30, 90 and 130. The students might have one or two of them in either order. Now what I would like to do is have 3 different fields where I can mark with a Y=Yes and N=No in a block. then it would change the Grand Total score accordingly.

View 9 Replies View Related

Calculate Totals

Dec 12, 2007

I have a spread sheet that is used to review calls placed by a call center.

Column A has the extensions of the phones, and Column E has the type of call (Outgoing or Incoming). Each line is a new phone call.

We have about 8 extensions, but the worksheet could have a couple thousand calls. So, Column A could have extension 1401 from rows 1-100 as extension 1401 made 100 calls. I'm looking for a formula or macro that will summarize how many outgoing and incoming calls extension 1401 had. Thoughts?

My initial thought was something like this:

=SUMPRODUCT(--(A2:A5000=H2),(E2:E5000))

In this formula, I would type in the extension of H2 and it would scan Column A and add up the values in Column E. The only problem is, that Column H doesn't contain a numerical value. It only has "Incoming" and "Outgoing" (minus the quotes), so this doesn't work.

View 9 Replies View Related

Adding Up Totals

Apr 30, 2008

Im trying add up totals on a list

a1b2c3a4b5c6a7b8c9a10b11c12a13b14c15

So for instance i want it to look at column A and find all the a's then then add the value of column b
so the total here would come out as 35

View 9 Replies View Related

Calculating Totals

Nov 1, 2008

I am attempting to build a spreadsheet for work, and I'm having trouble calculating a correct total.

In essence, the bit of my spreadsheet I'm concentrating on is 2 columns:

Column A is headed "Type of Work" and Column B is headed "Completed".

The Type of Work will either be Letters or Memos, and the Completed will either be Yes or left blank to indicate No. For other reasons, No has to be represented by a blank cell.

At the moment, I have 12 rows, going from A2 to B13, with data such as the following:

Letters Yes
Letters Yes
Letters
Letters
Letters Yes
Letters
Letters Yes
Memos
Memos Yes
Memos
Memos
Memos Yes

I need a summary section at the side with the following calculations:

Total Number of Entries
Total Number Outstanding
Total Number of Letters Outstanding
Total Number of Memos Outstanding

However, as I have only populated it with 12 rows of data for my testing purposes, I need to build formulas to take into account the fact that a maximum of 2000 rows may be filled in by other people over the next few months. Therefore, my formulas look like the following:

Total Number of Entries (stored in E2):
=COUNTA(B3:B2000)

Total Number Outstanding (stored in F2):
=E2-(COUNTIF(C3:C2000,"Yes"))

Both of the above formulas work perfectly, and when I enter a 13th and 14th row, the totals update as I want them to, so I have no problems there.

However, I am struggling to enter a correct formula which will calculate the total number of Letters that aren't Completed.

I did set some names and tried to use:
{=SUM((Type="Letters")*(Completed="Yes"))}

but it gave me a #NA error, presumably because the named range was looking at all the empty cells up to A2000.

View 9 Replies View Related

Sum Totals From Another Worksheet When (a And (b Or C))

Jan 21, 2009

I need and can't wrap my head around it enough to figure it out myself.

On one worksheet, I have raw sales data:

A B C D E F
Cust # Cust Name Product # Prod Name Sales Date
10001 whoever 800001 whatever 200 1/3/2009
10003 whoever2 800241 whatever 250 1/3/2009
10001 whoever 800060 whatever1 210 1/3/2009
10001 whoever 800055 whatever2 222 1/3/2009
Each product has multiple product numbers, so one product could have multiple product numbers: 800001, 800055, 800241, 800002, etc..

I want to count total sales by product, regardless of customer for a certain date.

I have the product numbers in their own cells.

I would like to say "If the product number is any of these, and the date is this, add all the sales numbers together and give me a total"

View 9 Replies View Related

Using IF AND To Track Quantity Totals

Jun 10, 2014

I am currently trying to find a better way to track quantities of delivered material from multiple suppliers. I have a attached a sample of what I am trying to do. In the "totals" sheet Row 3 is working as planned. There are a couple of issues that I am having with my formatting as is. First off in my "raw" data sheet there is an empty row between each row of numbers (this is they way I am sent the information from the supplier). This empty row causes my totals to place a Q everywhere column A on "Raw" is not filled with "B". Is there a way to have the totals sheet only pull from the cells with values in them?

My other question is how I would be able to get the values for material, Net wt., and Charges to populate if Column A in "Raw" is Q?

Quantity Tracking.xlsx

View 5 Replies View Related

How To Calculate Series Of Row Totals

Aug 7, 2014

I have a list of stores and the costs for various items.

Each store has (usually) more than one row.

Each store's items are totaled at the end of each line. Simple

What I do not know how to do is total all rows for each store, automatically. Like I said, I don't know how to explain it, so searching for it does not work out well for me.

Please see the attached sheet : How to Calculate Store Total.xlsx

View 9 Replies View Related

Getting Totals For All Male And All Female?

Feb 9, 2014

I would like to take all of the data from student summary sheet for each category and total it for all males and females separately on the second sheet. I am looking for a formula that i can put in each cell and calculate all of the totals, and then just drag down....

View 8 Replies View Related

Sum Up Totals In Lookup Table?

Jul 28, 2014

I need to make a program to take inventory at a bakery. We do this twice a day. Our products are loaves of bread. We have white, multigrain, soy and lindseed, low gi, and gluten free.

They are in pallets, trolleys, crates, boxes, pallet rows (part of a pallet) and individual loaves (singles)

I need to find out how many loaves that we have. How do I total them up?

View 3 Replies View Related







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