Dave H kindly provided the formula below to sum all cells in a range with multiple values: =SUM(COUNTIF(D3:Y3,"a*"),COUNTIF(D3:Y3,"b"),COUNTIF(G2:Y2,"c"))
however my supervisor has just informed me that any A,B or C that appears in the J column is worth 4 instead of 1. Very annoying not to be given all the facts beforehand
so
J3...K3...L3
A....C....B
will equal 6 instead of 3
I want the macro to go to column F and copy down from the row containing AO NAMES down to the row containing TOTAL with the value within cell C3.
So basically AO NAMES will be the starting point and TOTAL will be the end point for a value to be copied down in a different column.
Example: Value in C3 = EU11
Column B Column F
AO NAME EU11
Chris Wright 1355 EU11
Hazel Thomas 4015EU11
Julie Cunningham 8426EU11
Dawn Cumming 8748EU11
Jacqui Connolly 16597EU11
David Newton 17978EU11
Duncan Cowen 25781EU11
0EU11
0EU11
0EU11
0EU11
0EU11
TotalEU11
Problem description: I need to put together twelve arrows each comprised of four components; field point, front insert, shaft & fletch and nock. Therefore, I have forty-eight components in an Excel table. Each of these components may differ slightly in weight. My objective is to mix and match all of the components to create as many arrows as possible of the same exact weight.
Two questions. 1) How should I be thinking about setting up the problem? 2) Which Excel function returns the results I'm looking for?
Total weight
Point
Insert
Nock
Shaft
?
100.1
109.7
20
179.1
100.1
110
I need to return a value if multiple criterion are met from different rows. I tried an if and formule but will not get a value since information is not entered on the same row. My criteria is text and numbers.
View 9 Replies View RelatedI'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.
I basically need a formula which does the following to produce a total score:
[Total awarded score] / [Total available score of questions scored]
which equals
[(score <> "" / range)*weighting] / [if score <> "" sum weightings]
I've attached an example document where I think I'm getting close, just not sure if its possible.
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
I need a formula that will count all instances of A*, A, B & C in a row D3: Y3
so if
D3....E3....F3....G3....H3.....I3
D......C.....A*....A......E......B
would return 4.
I need to count values from multiple sheets. My first sheet is called "ALL" and the other sheets are numbered "1 thru 20"
The cell I would like the counted data to appear is E9 on the "ALL" sheet
The data will be from sheets 1 thru 20 counted if row H (on all pages) says the word MED
I can get excel to do this on a single page but across multiple pages I keep getting value errors.
I have this formula to give me the number of unique locations and it works fine for cell J1:
=SUM(IF(FREQUENCY(MATCH($B$3:$B$22,$B$3:$B$22,0),MATCH($B$3:$B$22,$B$3:$B$22,0))>0,1))
What I am trying to figure out is how to populate cells N4:N5 and N8:N9 for unique locations by Fruit & Vegtables by Area A and Area B. I just have some basic data for this example as the spreadsheets are 30,000+ lines long.
I have a spreadsheet with two different rating scales (People & Business) that have a value of 1-5 per person. From this I created another column 'Sorter' that gives a person a single value of 1-25 (5*5 possibilities from the two rating scales.) I am trying to place people into a table based off of the column 'Sorter' as shown in columns U-Y. The real table cleaned up is in the table tab.
View 5 Replies View RelatedI need a formula or function that will sum the number of occurences a
specific text string is entered into cells in column B. I only want to count
the occurences when a number that is in column A (on the same row as the
text) is equal to a specific number. Sounds easy but it's very frustrating.
I'm trying to figure out how to count by two criteria, only one of the criteria has a value among multiple values in a cell and the other is a range. I tried to use sum but it doesn't count the value and count doesn't recognize the range. I attached the file so you can see what we're trying to do.
View 4 Replies View RelatedIn what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,
Order No OP Posted Set Allowed Set
30761157 00100.731.500
3076115700100.500.000
3076115700100.600.000
3076461000100.000.000
3076461000101.050.500
3076524400100.000.000
3076524400100.550.500
3076639600100.000.000
3076639600101.180.500
3076810900100.000.000
3076862900100.000.000
3076862900100.000.000
3076862900100.000.000
formula that will return the number of "x"s or "o"s within the same cell.
The cell has values that are formatted in multiple ways for example: PXX--XXP, --XO, OXX--.
I want the formula to return 4 if the cells has PXX--XXP or 3 if the cells has --OXX etc.
I'm having a problem with a spreadsheet in Excel. I have in column terras, codti problem and several cells that are repeated. I'm stating that terra appear only once, the information in column each issue should appear in different columns with the sum of how often they appear and codti according to terra.
In excel is best illustrated what I mean!
In total, 5267 lines and need to do this with all. You can do this in excel?
I am looking for a formula which will allow me to count unique values in a database, based on multiple criteria. Sample file attached (Formula required in cells J and K).
View 6 Replies View RelatedI need to modify the underneath Count Array Formula to count unique values based on multiple conditions. I can get the formulas to work with NUMERIC values in Column A in the N1 & N2 tabs. However, I cannot get the formula to work when column A contains TEXT values in the TX1 & TX2 tabs.
I've attached the XL file for your review of the project.
=SUM(IF(FREQUENCY(IF(('TX1'!$B$2:$B$15=B2)*('TX1'!$C$2:$C$15=C2)*('TX1'!$D$2:$D$15=D2),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0)),MATCH('TX1'!$A$2:$A$15,'TX1'!$A$2:$A$15,0))>0,1))
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
I am trying to count the number of rows on sheets 11 To 2 in my workbook. I want to count the rows (excluding the header) on each sheet one at a time and place them on a "Summary" Sheet. So for example. The row count for Sheet 11 would go in cell B2 of the "Summary" Sheet. The row count for Sheet 10 would go in cell B3 on the "Summary" tab. The row count for Sheet 9 would go in cell b4 on the "Summary" Tab. I have taken a crack at the sub procedure but can't get the syntax and/or the logic correct.
Here is what I have so far:
Code:
Sub SummaryCalculations()
Dim lr As Long
Dim i As Long
Dim SheetArray(11 To 2) As Worksheet 'Sheets that the rows need to be counted on are 11,10,9,8,7,6,5,4,3 and 2
For Each ws In SheetArray
[Code] ...........
I'm trying to work out a way to use a MAX function and IF Function to determine how i can find the highest number since a trade began. For example, i have a countif function that is identifying each trade (Column B) so what i want to do is to find the highest number for each trade (Column A) which is represented by dummy variable 1,2,3,4 etc. Please see example below: what i want to do is write code which runs for 3500 lines and finds the highest price since trade began and trade's are represented by 1,2,3,4,5,6,7 etc.
Close
Number
TRADE PRICE
HIGH PRICE
0.6347
1
$0.63
0.6565
0.6373
1
$0.63
[Code] ........
I am trying to count the number of rows that have values greater than 10/01/2008 in either of two fields. I tried following formula but instead of giving total number of rows, it returns a random date.
View 2 Replies View RelatedI am trying to develop some code to serve as an 'undo' for several macros that I have. They each take a selected range and perform some changes to that range. I have managed to make some code that will undo the last macro run but would like to make something that can go back several steps. To that end, I have started with the following code but am running into an issue when I need to use a variant array to hold multiple arrays of a custom defined data type:
VB:
Type SaveRange
Val As Variant
Addr As String
[Code] .....
This declares some public variables I'll need, each as an array so that I can iterate through several steps of do/undo using the undoIndex. I then use them to save each range I am about to change by calling the following macro within my actual data-manipulating macros:
VB:
Sub Save_RangeForUndo(rng As Range)
Dim i As Integer
Dim cell As Range
undoIndex = undoIndex + 1
[Code] .....
If you look at the last line here, this is my problem; I can't figure out how to properly use an array to hold each instance of OldSelection() for later referral. Just for completeness, here is my actual undo macro:
VB:
Sub Undo_Operation()
Dim i As Integer
OldWorkbooks(undoIndex).Activate
OldWorksheets(undoIndex).Activate
[Code] .....
I need to get this OldSlctVariant()() array to hold each instance of OldSelection() so that I can restore them for each consecutive undo. I'm not very familiar with the variant type and anything more than the basic use of arrays.
I'm trying to match stock portfolios of mutual funds with stock indexes to find out how much the portfolios deviates from the indexes (active share).
The deviation is simply calculated as the percentage of the portfolio holdings not included in the index.
The data I have is 10 years of monthly portfolio weightings for 30 mutual funds and 8 indexes, organized with company names in column A and the percentage weight for each company in each month in the adjacent rows (see simplified screenshot). I want to match all companies with all indexes.
Twofold:
1)The mutual fund data comes from one source and index data from another. This means company names are not spelled exactly the same.
2)Within the mutual fund datasets, a company might be listed more than once with different names. The different names refer to different periods in time, so what should be matched with the index is the sum of these columns.
The specific task that I need to accomplish is to match company name in the portfolio with the index and then return the absolute difference between weight in the portfolio and the weight in the index for each month. (see screenshot).
Hence the result I'm looking for is a matrix of company names (of each portfolio) in col A and the absolute difference between the portfolio and the index weight in the adjacent row.
The best solution I've come up with is to device a list of all the portfolio company names, including "doubles", and then for each index, add these company names and copy the row weightings. This I'm not particularly happy with because index weightings would no longer sum to 100 and it would be difficult to check for errors. Also, it would still take a lot of time.
I require a Formula to calculate the INTERVALS (the number of Rows between
the LAST instance and the PREVIOUS instance in a column) between each
individual occurrence of any designated PAIR of Numeric values (single-digit
/ double-digit) in the same Row of the Named Range "Results" and return each
calculated INTERVAL result to a separate Column on the same Row of a New
Sheet - starting with the most recent ( the LAST) occurrence.
For instance, each time 80 and 87 appear together in the same Row, return the
INTERVAL by calculating the number of Rows between the LAST instance and the
PREVIOUS instance in a column - locate when both Numeric values LAST appeared
together and Count back to their PREVIOUS appearance together to get the
required Count; i.e. count from the Row ABOVE LAST appearance to the Row
BEFORE PREVIOUS appearance.
The results are returned to a chart / matrix layout: I have the criterion
vertically and horizontally and they are referenced using the horizontal and
vertical cell address that houses each criterion, and the results are
returned across the Row of the intercept of the vertical and horizontal
criterion. At some point both criterion values being referenced will be the
same, can the Formula return empty text "" when this occurs?
Example Chart / Matrix Layout:
Cell Ref. A2 and B1 criterion 80 and 80
Cell Ref. A3 and B1 criterion 81 and 80
Cell Ref. A4 and B1 criterion 82 and 80
Criteria B1 houses 80
A2 houses 80
A3 houses 81
A4 houses 82
A5 houses 83
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
Extract data with differing date formats that I need to convert to something consistent that I can format as a date.
This is an example of data.... all with general format at the moment.
2/28/2013 2:48:53 PM
1/16/2013 10:48:50 AM
12/17/2012 11:59:49 AM
I have used this formula to extract the date portion, but I can't get this to then format as date. How do I convert this to the julian date, so I can then apply a date format?
=LEFT(G9,SEARCH("/",G9)+7)
(The day portion of this date always has a leading zero).
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
I need a Macro (not formula) which compares the comma separated values present in Column "I" with individual values present in Column "D" and generate the count of unique values in Column "J".
The sample sheet has been attached for reference.
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I have attached sample sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.