Counting/Summing Items
Jul 20, 2009
I have a separate sheet with the followingvery simplified as I probably have over 300 lines of dates and dollars)
Date Dollars
2/3/2009 $25
5/3/2009 $30
5/4/2009 $50
7/4/2009 $100
8/7/2009 $25
On another sheet, I want to count the number of items by month submitted and sum the $ amount.
January $0
Febuary $25
March $0
April $0
May $80
June $0
July $100
August $25
Through December
Pivot table would be best if possible....but any function that would work would be fine.
View 9 Replies
ADVERTISEMENT
Jun 18, 2007
I have a worksheet that has values in column F. I would like VBA code that will sum all the highlighted items in column F.
View 9 Replies
View Related
Nov 24, 2009
Items, of 3 different groups, and their values are entered in a table progressively, over a year period . I am trying to work out a way of getting the month total value of each of the 3 groups seperately, for each month of the year. A sample worksheet is attached
View 10 Replies
View Related
Jan 24, 2014
I have columns (1-7) containing values of time these columns are labelled G,R,A,S,D,B,T by 850 rows (which are locations/jobs)
The next set of columns (1-7 determins what week the work takes place) so you will get a G in a cell or GR etc.
I would like a formula to work out the sum of the time columns by the code in the corresponding cell - I am stuck!
Seantc example.xlsx
View 7 Replies
View Related
Jun 4, 2014
Find the attached excel sheet : Example Statistic.xlsx
I am trying to calculate the crew hours from a database I created .
I entered the sumifs formula but i entered many argument .
The block time should be calculated based on the month and to calculate only for specific name for each crew .
The formula should be involve in the colorful cells only ...
View 4 Replies
View Related
Feb 5, 2008
I am looking to effectively complete a sumif formula but not summing, counting. I thought a countif would be a simple solution but the countif is different i.e.
SUMIF (range,criteria,[sum_range])
COUNTIF(range,criteria)
I want to be able to put =countif(b1:b10,A1,c1:c10) as you would with a sumif
but I am told i have put too many arguements so what shold i be using??
View 9 Replies
View Related
Apr 26, 2008
I have a set of data that I would like to break into groups, but I do not know what the groups are. I would like Excel to help me find the groups.
More specifically, my tax data consists of the following columns (I'm simplifying): parcel number, dollar value, tax amount, days late paid.
123435, $12000, $100, 20
234234, $23000, $230, 05
etc.
Of course my Excel "results" would omit the parcel numbers, but it would propose groups (and how many parcels in each group) such as: ...
View 6 Replies
View Related
Aug 14, 2014
I have a spreadsheet that I use to log incidents. In column "A" I have the date in month/day/year format (8/14/2014). I am attempting to automate the graphing process so I have a graph that will tell me how many incidents I had in the month of August. I have attempted the following formulas (and other similar formates with minor changes)...
=COUNTIF('CY14'!A2:A500,"8,2014") and changed around the date format
=COUNTIF('CY14'!A2:A500,"&DATE(8,2014))
=COUNTIF('CY14'!A2:A500,"August")
All of these come up with "0" while I have 17 listed incidents for August. Am I doing something wrong, or is this not possible.
View 4 Replies
View Related
Sep 15, 2008
I want to paste a list of customers into a sheet and then have a formula that counts the number of customers. So, even if a customer appears 10 times it would only count it once and and continues down the column and counts the next company name and so on. Then it gives me a total of the number of different companies in that column.
View 9 Replies
View Related
Nov 5, 2008
I need a formula which can count the number of items which have been applied to a suspense account in between a set of dates so for example i may need all items placed into a suspense account in the month of october. i record all items in a list so i do have a list of dates i just need to know how to count them and keep a running total as i need to feed it into another spreadsheet. I will also need to sum these the format of the spreadsheet is below -
******** ******************** src="http://www.interq.or.jp/sun/puremis/...<CENTER><TABLE cellSpacing=0 cellPadding=0 align=center>Microsoft Excel - Book4___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2K2B3K3B4K4B5K5B6K6B7K7B8K8=ABCDEFGHIJKL1MOPO/S Date AppliedValue DateRemovedValue AgeCompany Name2Cheque 01-Nov-08(15.55) 01-Nov-08 15.55 0abc3Direct Receipt 01-Nov-08(109.00) 01-Nov-08 109.00 0def4Cheque 02-Nov-08(50.30) 02-Nov-08 50.30 0ghi5Cheque 03-Nov-08(23.48) 03-Nov-08 23.48 0jkl6Direct Receipt 03-Nov-08(2,236.46) 03-Nov-08 2236.46 0mno7Cheque 03-Nov-08(212.11) 03-Nov-08 212.11 0pqr8BACS/CHAPS 04-Nov-08(58.75) 04-Nov-08 58.75 0stuSheet1 [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
May 8, 2009
{=SUM((C2:C8="black")*(B2:B8="sneakers"))}
I’ve got following formula counting different kind shoes that are black and sneakers and above formula work very well. What I wonder is if you can convert above formula to count these items in a filtered list. Could you use the subtotal-function in any way?
View 9 Replies
View Related
Sep 8, 2006
In column A, it contains the monthly salary data of each employee, then in column B, it contains the classification level data, Like Grade A, Grade B and Grade C. In this exercise, it want to find out the statistic as follows:
1. How many employee's monthly salary is below 10K and their classification level is Grade A, B or C
2. How many employee's monthly salary is above 10K and their classification level is Grade A, B or C
So any formulars can do that in instead of using the sorting method?
View 3 Replies
View Related
Jan 19, 2014
1. I use this formula to count a number of items in a column per month: =SUMPRODUCT(--(MONTH(B6:B500)=8))
I also have another column C where I put some descriptors against the values in column B. I need a formula to calculate a number of items in Column B, except for those items, which are equal to XYZ in column C.
2. I also calculate a number of items, which were completed in a given month. Completion is marked with "Y" in a separate column (D). I use this formula: =SUMPRODUCT((YEAR(B6:B999)=2013)*(MONTH(B6:B999)=12)*(K6:K999<>"Y")). I can't make it count a number of completed items in January. C
View 8 Replies
View Related
Aug 3, 2006
I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.
so,
column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1
now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter
now what should i suffix or prefix to this formula to count unique values in column A.
View 11 Replies
View Related
Oct 10, 2008
I am trying to count the number of unique items in a single column (~5,000 rows of data). For example, I may have the following data
a1 = apples
a2 = pears
a3 = oranges
a4 = apples
a5 = apples
a6 = apples
a7 = pears
in this case number of uniques items is 3
Right now I am using a Pivot Table to figure out the number of unique items but I am sure there is an easier way to do this.
View 6 Replies
View Related
Feb 3, 2010
I need to identify the number of occasions on which a product type is bought by a customer in isolation from other product types. I have attached a sample to illustrate. The actual data is more complex and is actually medical data concerning issue of oral or IV drugs. There are many thousands of records.
To clarify, in the example, there was only one occasion when Bread was bought on its own by a particular customer on a particular day. The way the data is presented, 'Bread' could be listed before 'Milk' or, as with 'Steve' on the 2/4, it could be in the middle of a series of 'Milk' purchases. I can sort by date/name/type, but I cant work out a formula to resolve the count.
View 3 Replies
View Related
Apr 2, 2012
I need to be able to count items if they match a condition in another column, as below
A B
1 Apple Red
2 Apple Green
3 Apple Red
4 Pear Green
5 Apple Red
6 Pear Green
7 Pear Red
8 Apple Red
How can I count how many Red Apples there are. The answer should be 4 but when I use countifs I can either count the no of apples in Col. A or the no of colour Red in Col. B.
I have tried every combination of if, vlookup, and count that I can but keep getting errors or single col. counting.
View 3 Replies
View Related
May 20, 2006
Suppose I delete a (unknown) number of items from the Worksheet Menu Bar. This can vary because of the fact that my excel sheet is being used by a different number of people, all with a different Worksheet Menu Bar configuration. Is there a way of counting how many Menu items are still left in the Worksheet Menu Bar?
So, suppose all that is left (after using the program) in the Worksheet Menu Bar are Excel, File, Tools, Help. How can the porgram find out that there are only 4 items in the menu?
View 3 Replies
View Related
Aug 13, 2014
I have a spreadsheet that lists a set of actions and the days they need to be completed.
The due dates are listed in column c and in column h I have a the user choosing "completed" from a drop down data validation.
I have a dashboard on a separate worksheet, showing if the item is complete and the due date.
Ideally I would like to display the dates on the dashboard in red if they are past due, and the box in column h was not marked complete.
I have been going through my books and trying to solve this, but I run into a different issues.
Even if for now If I can get the main worksheet to display the color conditional formatting that would work
View 2 Replies
View Related
Mar 27, 2014
I need to count the total number of times 4 different values appear in a column. This formula works for one value:
=COUNTIFS(Source!$C:$C,$B5,Source!$J:$J,$L$3,Source!$L:$L,$H$1)
Where H1 contains the word Assigned. I need to also find and add to count for matches in I1,J1 and K1 which contain New, Pending and Work in Progress respectively.
View 6 Replies
View Related
Dec 26, 2013
I am wanting to create a list where I can select multiple items within that list and then print only those selected items. I have created something similar thru data validation, but I can't get it to print.
In addition, I would like to be able to subdivide the list into multiple categories, then select items from these multiple categories and print them.
View 3 Replies
View Related
May 23, 2006
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..
View 4 Replies
View Related
Jan 1, 2014
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13
H1 Total of matching numbers in cell range K1:P11
View 3 Replies
View Related
Jul 17, 2009
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
View 9 Replies
View Related
Feb 3, 2014
I was given this spreadsheet to count attendance by entering the entry date and exit day, however it's counting the first day and the last. I'm needing it to only count the first day and not the exit day.Book2.xls
View 1 Replies
View Related
Oct 26, 2008
I am trying to sum up a list of cells that have formulas attached so if there is no number in the formula it shows up as #N/A how do I make it so wherever there is an #N/A it equals 0?
View 9 Replies
View Related
Jan 15, 2009
The formula is designed to sum a set of data based on 22 variables between a certain date range. In order to keep the formula manageable, I have grouped the variables into arrays.
EX.
Array 1. - 20 potential choices (Service codes)
Array 2. - 2 choices (pkg types)
Array 3. - 2 choices (volume type)
Array 4. - 3 choices (company names)
Each array (listed horizontally on one spreadsheet) calls a specific column of data to match from a different worksheet. I have no problem if only applying one array but multiple arrays return incorrect values.
View 9 Replies
View Related
Jul 6, 2009
I have a table that have a test or character and value (number) like
A 5
B 6
C 3
And another table that has a set of characters that I want to sum the value of the characters like
A B C 14
So that the result of summing the characters will be in the next cell.
View 11 Replies
View Related
Feb 18, 2010
I am having real trouble with a formula.
I have used a similar formula for to calculate a column.
Can any one see where I am going wrong. It is the cell highlighted in yellow on the attached spreadsheet.
View 14 Replies
View Related
Oct 22, 2008
I'm having a problem summing after running arrays to analyse data. I have a list of four possible outcomes and I need sum up the totals, but sometimes there are no results, so there is #DIV/0 in the list I need to find the total for.
is there some code or some kind of formatting that would give me a total even if #DIV/0 is in the list?
View 3 Replies
View Related
