I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:
=SUMIF(J19:J40,"Y",H19:H40)*0.08
I have been trying to search an array and cannot find the correct combination of functions to get the information I want. I might need a macro to do this but I will try anything. Here is an example:
1 A 1
1 B 2
2 A 3
2 B 4
3 A 5
3 B 6
I want to search for 3 and A and return the value in the 3rd column. I have tried several functions but cant figure out how to make sure I get the correct value in the 2nd column that corresponds to the value in the 1st column.
summing monthly values based on different date ranges. For example if I have multiple contracts with different start and end dates and based on those date ranges, I want to sum the respective monthly production totals, how would I do that in a formula. I started to set up the table to include "start date month" and "# of months in contract". Is there a way to sum based on the start month and # of months in contract.
For example if hte contract started in Jan08 and ends in May08, the calculation would start with month "1" and sum over 4 months.
Here is an example: ....
I am currently trying to reconfigure a dataset into something more managable for our regression needs. The issue I am having is trying to automate as much of the data transfer as possible. The data set is set up as follows (for example):
Yr Tag Value
0 2 #
1 2 #
2 3 #
3 3 #
0 1 #
1 1 #
2 1 #
3 1 #
etc. where tag is a special code we have developed based on the number 1, 2, 3, and 4
What I would like to do is develop a summing formula so in year 0 I can sum all the values if the tag is equal to 1 and then I would do that for the numbers 1 through 4 in my "compilation" worksheet.
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be
"A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
I'm making a spreadsheet that tracks instances when something goes above or below a threshold. Anytime it goes below, a column populates with the number 1, and if it happens to go below a threshold on a Friday, a separate column populates with the number 2. If its doesnt go below, the column with 1 does not populate, however, every Friday populates with a two.
I need a column that adds the 1 and the 2 when the threshold is broken on a Friday, but only shows the 1 when the threshold is broken on a non friday and shows 0 when the threshold isn't broken, but happens to be a Friday.
I am not sure my title is accurate so I will explain what I am trying to do. I would like to total the numbers from each row which has "Monthly Totals" in column A. There are currently only two of these but there will be more added over the year. Rather then have to edit the formula to include the new cell I want added to the total, is there a way to create a formula which will automatically scan Column A for "Monthly Total" then add the cell from that row to the running total at the top? Basically, If any cell in Column A has "Monthly Total" add the cell from column B (from the same row) to the running total in cell B1. This way I could add as many "Monthly Totals" as needed and I wouldn't have to edit the formula in cell B1.
View 7 Replies View RelatedWhat formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
View 2 Replies View RelatedI am looking sum formula to display my data with 3 criteria (display by this month, until this month & until last month) based on header column/correspondents, then in cell L3 as selected month display..
For further information, check workbook attached...
SUM 3 MODELS MONTH.xlsx
I have a table of data: - In cell A1: to C7
A B C#Group Aug-091Merchant Bar2002Plate3Structural - HR1004RHS5Plate3006Structural - HR300
What I would like is a formula that sums up column C if Column B of that row is "Plate" for example
Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.
I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.
For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.
Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.
However, someone high up in management in my organization would like to "drill down" into the data behind the
SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)
If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.
At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.
I'm having trouble with doing a similar thing with conditions/criteria based on arrays for different formulas (e.g. IF, COUNTIFS, SUMIFS).
Here's what i'm trying to condition and than use different formulas:
column A - Region
1. Europe
2. Europe
3. Asia
4. Asia
5. America
6. Asia
7. America
8. America
9. Europe
10. America
[Code] ....
QUESTIONS
1. (IF, AND ?)
For all cases listed in column A that have "university" in the column B, If there is at least one case having "Europe" write 1, if there isn't write 0.
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to produce 1.
If there are no cases like that, I want formula to produce 0.
* In this example the formula would produce 1. for "Europe" and "university".
* If the formula was looking for "Oceania" in the column A with "university" in column B it would produce 0.
2. Using COUNTIF with multiple criteria.
Count all of the cases that have "Europe" in column A and "university" in the column B
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to do count.
* In this example the formula would produce 2 for "Europe" and "university".
* If the formula was counting "Oceania" in the column A with "university" in column B it would produce 0.
3. Using COUNTIFS with multiple criteria.
Count all of the cases that have "Europe" in column A, "university" in the column B and "GER" in column C
The conditions are "Europe" in column A, "university" in column B and "GER" in column C.
* In this example the formula would produce 2 for the 3 conditions.
* If the formula was counting "Oceania" with "university" and "GER" it would produce 0.
I've spent a lot of time searching the Internet for this (e.g.how to use IF with an array, about different functions and formulas...) and found nothing that works.
For COUNTIF(S) they just didn't work, event with ctrl+shift+enter.
I have a list of payroll data (Hours, Rate per hour & Total) for a number of workers. I want to summarize the data in ranges of hours and by ranges of rates in a table. This will then show me in what ranges of Hours & Rates is creating the most value (Total). See attached RangeSummary.xls for example data and output.
View 3 Replies View RelatedThe following table is used in the sumif.
I would like the sumif to use the range with the names, and the range with the dates.
I have a GUI in which the user enters a name and a date and the $ amount earned for that week should appear.
The criteria is the name and the date.
******** ******************** ************************************************************************>
Microsoft Excel - Business Book.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutP20=
IJKLMN3******4michaelb1*14$400.007/21/20025Michaelb1215$450.007/21/20026
[Code] .........
SUM formula. My spreadsheet has roughly 8000 rows of data listing client info for how many commercials were purchased on Cable Networks a (a simplified example)...
ABCDE1ClientNetworkDay-partPrioritySold2Client AHGTVAccess35063Client BHGTVPrime45014
Client CHGTVDaytime30095Client AHGTVPrime45096Client DHGTVPrime35037Client BHGTVPrime5502
What I am attempting to do is on Sheet2 summarize the commercials sold based on a starting Priority. So if the priority code in Column D is 350, I need to sum all spot sold at a priority of 350 and higher for each day-part and Network
ABCD1NetworkDay-partPrioritySold2HGTVPrime350 and up
?3HGTVPrime400 and up ?4HGTVPrime450 and up
?5HGTVPrime550 and up ?6HGTVPrime600 and up ?7HGTVPrime650 ?
I've attempted to create helper columns to tie all the necessary PR codes together and then SUMIF by the different helper columns, but I'm not getting the correct result. Instead of getting "15" for Sheet2, D2... I'm just getting "3"
I am attempting to count records based on 3 criteria in 3 different ranges and am hitting a wall. My formula will be on a different worksheet as it is the summary sheet.
Worksheet "Cases"
Column E - Type
Column F - Month
Column G - Year
Worksheet "Recap"
Recap month will be input into Cell I5 eg. "6" for June
Recap year will be input into Cell K5 eg. "2009"
Cells N10-N15 is a list of Types
Summary Cell J10 will need to count if
1)worksheet Cases column E = worksheet Recap N10
2)worksheet Cases column F = worksheet Recap I5
3)worksheet Cases column G = worksheet Recap K5
I have a worksheet that has data in columns a through d. Acct#, Name, Usage Code, and Other Code. Acct# and Name are self explanatory, usage code is a two letter code, and other code is a field I added to use with my macro. A specific account number could be repeated many times, but it is the usage code I am needing to work with here. My acct numbers are sorted in numerical order and column d has a 1 each time a new account number appears and a 2 if it is a repeat. I am wanting to copy the account number of any account that does not have a specific usage code listed at least once. That specific code is "ta". I am wanting it to only list the account number once in column f starting at row 2. Here is my code that is not working.
Sub test()
Dim CopyNum As Integer
Set CopyNum = 2
Dim Switch1 As Integer
Set Switch1 = 0
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
I need to sum the items in column G12:G39 with the 1st name in Column B12:B39 which could be 1 of 9 different names, that, and the 2nd name in column D12:D39 cannot not say either #4 or TE. If it says #4 or TE, then it needs to sum the info in colmn G12:G39, based on the 1st name in column B12:B39.
View 9 Replies View RelatedI track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I have a spreadsheet which has data in rows. I need to sum the first 5 values from the left of the range which are greater than zero. BUT if there are less than 5 non zero values present I want to sum from the right of the range.
First condition:
1 2 3 4 5 6 7 8 9 10
The function would return: 1+2+3+4+5 = 15
Second condition:
0 0 0 4 5 6 7 8 9 10
Function would return: 4 + 5 + 6 + 7 + 8 = 30
Third condition:
0 0 0 0 0 0 0 8 9 10
The function would return: 10 + 9 + 8 = 27
I have had success with:
=B4+SUM(SMALL(IF(I4:O4<>0,I4:O4),ROW(1:5)))
(using ctrl shift enter)
BUT it crashes when faced with the third condition.
I would also like to avoid ctrl-shift-enter functions if I can as I don't have much success copying them around the spreadsheet.
I am looking for help in developing steps in a spreadsheet to calculate a sum based on a few conditions. The basis of my spreadsheet works fine, however I am trying to add the contents of a cell to another by checking other cells.
For example:
Cells A1:A10 contain a numerical sequence (1-10 respectively) to indicate a "Node"
Cells B1:B10 contain a user selected number (1-10) which defines the Node it is fed from. We'll call this "Fed From Node".......ie. If Cell B2=1, that means that Node 2 is fed from Node 1.
Cells C1:C10 contain a value which is input by the user. We'll call this "Amps"
Cells D1:D10 would show the total "Amps" of the current cell added to all others that are fed from same.
What I am ultimately trying to figure out is how to add the "Amps" to each "Node" and show the results in column D.
More specifically if the spreadshet looks like the following: ...
I want to sum certain data, which meets two conditions.
My data set contains three columns and a lot of rows. The columns are the following:
PostalCodeDeparture
PostalCodeArrival
PassengersInCar
I want to sum the total number of passengers with departure postal code 5100 and arrival postal code 5110. (and I want to do the same for all other postal code combinations in the data set)
With "SUMIF" I can only include one condition.
I could probably fix by making a loop, but I am trying to avoid doing any more looping. The program that I am working on has way too much looping in it already, and is getting kind of bogged down.
Here is what I need. On my worksheet, column D lists the names of farms, column E lists a specific chicken house on that farm (by number), and column F lists how many chicks are being placed into that house. What I need to do is find a way, within VBA, to add up all of the chicks in column F that correspond to a certain farm name (column D) and chicken house (column E).
I can make something like this work in excel, but not in VBA. For example, if I wanted to find out how many chicks were being delivered to Johnson Farms, house 4, I would use this formula:
= SUM(IF(($D$5:$D$25="JOHNSON FARMS") * ($E$5:$E$25=3),$F$5:$F$25 ))
I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP.
I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them.
Example
C F G H
CUSTOMER JULY AUGUST SEPTEMBER
customer1 2 3 4
customer1 2 3 4
customer2 2 3 4
customer2 2 3 4
The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H).
The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would:
sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName
I'm on Vista with Excel 2007, HOWEVER, the solution must be Excel 97-2003 compatible.
I have two columns. One will a Y or N in it, the other will have a number. I need to sum the column with numbers, but I ONLY want the numbers added that have a 'Y' in the same row of the other column ...
I have monthly sales, and i wanna be able to sum the last-twelve month sales, when i select the month from a drop-down list. For example, when i select Feb14 from the drop-down, I want the formula to sum the sales between mar13-feb14. how can i accomplish that?
Select Month
Feb14
LTM Sales
?
[Code] ..........
I need to sum a value that falls between 2 date ranges. I have read a few posts on this subject and thought I had it working, but I realized I was getting values in fields I should not be.
=SUMPRODUCT((Details!H2:H100>=$E$4)*(Details!H2:H100
Need formula which can sum Amounts from varying Weekly time periods and the result be recorded in the appropriate month? I've attached a simple example of the way the output needs to look and a sample data table below.
View 3 Replies View Related