Count + condition

Hi again!

My other problem is that I want to get the number of different
variables (text type) + a condition (date)

Example:

Col 1
AAA
AAA
BBB
BBB
BBB
AAA
CCC
DDD
AAA
EEE

Col 2
2004-08-01
2004-08-01
2004-08-01
2004-08-02
2004-08-02
2004-08-04
2004-08-04
2004-08-04
2004-08-04
2004-08-04

I should get the numbers of different variables for each date 

2004-08-01 -> 2
2004-08-02 -> 1
2004-08-04 -> 4


thanks


---
Message posted from http://www.ExcelForum.com/

0
8/12/2004 2:11:14 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
261 Views

Similar Articles

[PageSpeed] 24

Hi
if you create the list of unique dates on a separate sheet 
(e.g. using 'Data - Filter - Advanced Filter') in column 
A, enter the following array formula in B1 (entered with 
CTRL+SHIFT+ENTER):
=SUM(IF(FREQUENCY(IF(('sheet1!!$B$1:$B$100=A1),MATCH
('sheet1'!$A$1:$A$100,'sheet1'!$A$1:$A$100,0),""),IF
(('sheet1'!$B$1:$B$100=A1),MATCH
('sheet1'!$A$1:$A$100,'sheet1'!$A$1:$A$100,0),""))>0,1))




>-----Original Message-----
>Hi again!
>
>My other problem is that I want to get the number of 
different
>variables (text type) + a condition (date)
>
>Example:
>
>Col 1
>AAA
>AAA
>BBB
>BBB
>BBB
>AAA
>CCC
>DDD
>AAA
>EEE
>
>Col 2
>2004-08-01
>2004-08-01
>2004-08-01
>2004-08-02
>2004-08-02
>2004-08-04
>2004-08-04
>2004-08-04
>2004-08-04
>2004-08-04
>
>I should get the numbers of different variables for each 
date 
>
>2004-08-01 -> 2
>2004-08-02 -> 1
>2004-08-04 -> 4
>
>
>thanks
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
frank.kabel (11126)
8/12/2004 2:28:15 PM
Reply:

Similar Artilces:

Counting columns of data that has been conditionally formatted
Hi I have twelve columns (months)of sales data (percentages calculated from another sheet) that are conditionally formatted to highlight the highest and lowest percentages each month (Two cells highlighted each month). What I want to do is create two columns at the end to count the number of times the subject has been the highest in the preceding twelve months and the lowest in the preceding twelve months. I need to count the number of highlighted cells. I have tried several formulations but have got nowhere. I will be grateful if anyone could help. Regards Mike "Mike"...

Rota spreadsheet
Hope someone can help as I'm at a total loss. I've got a rota that shows all personnel details, with shift patterns: 9am - 3pm 8am - 4pm 10pm - 4am etc (shift patterns subject to change and are individual) I have a column that shows them 'clocked on' and another for 'clocked off'. Now for legal purposes I need to know who is 'on' at regular intervals: 7 am 7.10 am 7.20 am 7.30 am so thinking logically I'd say if for the 7 am count if they are 'clocked in' but not 'clocked off' then count that person but I can't seem to find the r...

Conditional Formating based on Date
I would like to change the cell color based on date. For example if date is today or less color should be red, if date is upto 10 days from today the color should be yellow. I used following: Condition 1: =A1<=today() RED Condition 2: =A1-today()<10 YELLOW it worked ok if I have date in the cell. If the cell is empty, it is still showing me RED color. I want see no formatting if the cell is empty. Thank you in advance for your kind help. Change condition 1 to: =3DAND(A1<=3DTODAY(),A1>0) Regards, Per On 7 Mar., 21:47, Kimti <Ki...@discussions.micr...

Conditional formatting and If function?
Can this be done? I want the value of cell g4 to depend on the text entry to cell f4 If f4 = a then g4 displays 25% value of cell c4 If f4 = b then g4 displays 50% value of cell c4 If f4 = c then g4 displays 75% value of cell c4 If f4 = d then g4 displays 100% value of cell c4 Can this be done -- don ----------------------------------------------------------------------- dond's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2547 View this thread: http://www.excelforum.com/showthread.php?threadid=38930 Hi, This has nothing to do fith conditional formatting, I...

2002: Conditional formatting question
Hi! I am using Excel 2002. I want to set up a conditional format that look for a piece of text. Anyplace in column A (range A:A) where the word "work" in any format (eg. WORK, Work, work, WoRk, work_, work-, etc.) is found I need the background color changed. Other than the search function what can be used to do this? I have tried everyway I can think of to get search to work, but because it wants a cell reference or a piece of text I have not been able to make it work. This would be really easy if one of the options was "contains" rather than expecting the values ...

Count right digit
Cell values in EX12:EX60 are "I" in some cells and "RI" in others. I want to count how many cells have an "I" in it (including the "I" in the "RI"'s) in the range EX12:EX60. I can count the "I" in "RI" in one cell (EX60) only with this function: =IF(RIGHT(EX60,1)="I",COUNTA(EX60),""). However, I want to count the "I"'s in the whole range (EX12:EX60) I tried: =IF(RIGHT(EX12:EX60,1)="I",COUNTA(EX12:EX60),"") and get errors. Any suggestions? =countif(ex12:ex60,&...

Multiple criteria count
Hi ppl, Well what i want the formula to calculate is, count the rows whic contains lets say "Companyname" in column B and "yes" in column I. But with the formula i use now it doesn't include the rows wit "Companyname USA" so how can i achieve this ?? And this is what i got so far: {=SUM(IF('2003'!B1:B1500="companyname";IF('2003'!I1:I1500="yes";1;0)))} Thnx in Advance Siny- ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://...

conditional values
I have three columns, each has a yes or no question that needs a response in rows 1 - 1000. (a "1" if yes, or a "2" if no). If any of the three questions is answered with a "1", I need to populate another column (labeled "Action Required") with either "Yes" or "No". The only time the "Action Required" will be "No" is when all the questions contain "2" as the response. if any one of the questions contains a "1" I need the "Action Required" to be "yes. Does anyone have a solutii...

COUNT ON NON-CONTINOUS CELLS
Hi, Is there a way for me to get a count of cells (NOT in a RANGE) that have values greater than zero (0). i.e. A1=1 C3=0 D4=3 E2=0 E5=2 Result = 3 Thank you! Hi Gerard Try this =COUNT(IF(A1:C5>0,A1:A5)) This is an array formula you must press CTRL+SHIFT+ENTER to enter the formula. If successful in the Formula Bar you can see the curly braces at both ends like {=COUNT(IF(A1:C5>0,A1:A5))}. do not type them in yourself. HTH John "Gerard Sanchez" <geepeeoneREMOVEME@gmail.com> wrote in message news:OX4V5F5YKHA.5852@TK2MSFTNGP05.phx.gbl... > Hi, > > Is ...

Mail count is not correct
Hi, I have my client setup to download mail to local system when I have new mail. My Outlook is showing me the number of mails like 10. But when I count my mails I have only 7. Where is other three mails?. I have this issue, Pls someone help me what is going there. Thank you, Pacha. Check your deleted or junk file, most likely they are there. Or if you have selected a rule to remove from server, it downloaded the total message count, but when it got to the ones you want deleted it did as it was told. The count of emails is usually correct. The only time that you might see a extra email ...

Display report if meets condition
Hi, I have a report which is run from a button on a form. The report contains a subreport which has a calcualted field displaying a total of all items on the subreport. I have created a macro and attached it to the On Format property of the report detail section to check if the total exceeds 5000. If the the total exceeds 5000, a message box displays. I would then like the report to close the report and return to the form so the user can make adjustments. However, the Close action Halts the macro. Any ideas would be appreciated as to how to make this work. TIA, Marie -- Message posted vi...

Counting the Same Rows
I have a spreadsheet with 2 columns column a is CustomerID and Column B is TransactionDate I need a way of counting how many transaction dates a customer as column A can have the same customer ID in multiple times Any help appreciated Steve -- Steve M ------------------------------------------------------------------------ Steve M's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32520 View this thread: http://www.excelforum.com/showthread.php?threadid=534495 Is it just =COUNTIF(A:A,"Customer A") or do you need to test the dates as well =SUMPR...

Count Function
I am creating a report for the number of pupils being collected at a certain collection time e.g. 14:00hr and 15:00hrs using the formula =Count([Child Last Name]) in the collection time footer However when i try to count the overall number of pupils in the footer using the same formula, 'Error' appears on screen? Thanks There are potentially a dozen footer sections in a report. Which one is giving you a problem? What is the name of the control? What is the exact formula? -- Duane Hookom Microsoft Access MVP "ELo" wrote: > I am creating a r...

COUNT ACROSS SEVERAL PAGES
=COUNTIF(A:Z!B335:B343,B22) When using this to count between several sheets I return an error, but cant work out where I am going wrong. any help would be great Pete -- (][ THIS EMAIL HAS BEEN SCANNED BY NORTON ANTIVIRUS ][) One way =SUMPRODUCT(COUNTIF(INDIRECT("'"&MySheets&"'!B335:B343"),B22)) where MySheets is a named range with a list of all worksheet names (insert>name>define) or you can use the range reference itself like H1:H26 (if you use the 26 sheet names), note that each sheet name that you want included has to be there -- Re...

Counting Records after filtering by feilds
I am working on a project that will create a report to count the number of open work orders (records that have their opendate field with in a date range given), close work orders (records that have their closeddate field with in the same date range given earlier) and pending work orders (records that do not have an entry in the closed date field). I don't know how to (a) prompt the user for a given date range, im thinking of maybe using a form interface? Is it possible for me to have a calander button to click to select a date from there? and (b) To count the what was previously describe...

count
does anybody know how i can do the following. i have a spreadsheet with the following. fields. country, weight, dates I want to do a countif the country = argentina and the weight = 2lbs. tx in adavance. Any dates? =SUMPRODUCT((A2:A100="Argentina")*(B2:B100=2)) assuming that you enter pounds as numbers and not 2lbs -- Regards, Peo Sjoblom "nkob" <tlee@lang.com> wrote in message news:%23bVsmhpjDHA.2592@TK2MSFTNGP10.phx.gbl... > does anybody know how i can do the following. i have a spreadsheet with the > following. fields. > > country, we...

Counting 11-02-07
I have a database that I would like to create a table that would allow me to added document and page information. I would like to know how I can do vba or query to do this: 000001 000002 000003 000004 So I would want to have those numbers increment by one for each pay I have of images. Is there anyone out there that can help me? How are you putting data into your table? "lauriecking@gmail.com" wrote: > I have a database that I would like to create a table that would allow > me to added document and page information. I would like to know how I > can do vba or query to...

Counting checkboxes between two dates
I'm tring to build a query that will count the number of checkboxes between the dates of two other fields. I have two tables within the query. One table (tblInspection) contains InspectionID, InspectionDate, and Citation, among others. The other table (tblReinspection) contains ReinspectionID, and ReinspectionDate, among others. There is a one to many relationship between the two tables, tblInspection being the parent table. Only one InspectionDate will be entered but none or many ReinspectionDates may be entered. The Citation field is a checkbox. What I am tring to do is to find all the ...

Question about syntax for conditional formatting
See below: Can someone explain the following in simple language; =AND($B$12<>"",$B$12<TODAY()+120) What does the <> after the cell location mean? and why the double quotes ""? I thought the double quotes meant whatever is in the cell is NULL....I have a date in the $B$12 position. Hi The formula will evaluate as TRUE or FALSE and is checking 2 conditions which must both be TRUE =AND($B$12<>"",$B$12<TODAY()+120) the AND is the bit that ensure both must be true to return TRUE Condition 1 checks that B12 has data ...

How do I get the sentence count in a word document?
-- Many thanks in advance best regards Ian Word Count provides a count of words, characters, lines, and paragraphs but not sentences. Although a macro can be written to get a rough count, Word can't necessarily distinguish between the period at the end of a sentence and the period following an abbreviation. There must be some algorithm, however, as the "readability statistics" are based to some extent on the number of words in the average sentence, I think. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word...

conditionally linking to different cells
Hi! I maintain a worksheet that displays yesterday's revenue. I would like to be able to see any particular day's results just by imputing the date at the top. Basically, I would like to put the date in cell A1, and in the main area of the worksheet all of the numbers for that day are pulled. All of the raw data is located to the far right, so it would just be a matter of conditionally linking to that information based on what day it is. Is this possible? Even if it is not possible using dates, is it possible by any other method? Thanks! It is possible, assuming the rev...

Conditional count if function
Hello, Can any one please let me know how to take the count pertaining to a condition, Example: Range of Values in a colomun and need to take the count of the value's in range between i.e., The values are from A to Z in a column, find out the count of value if "A" < = > "B", or the count of "A" is in between the range 1 to 2 See SUMPRODUCT in Excel Help -- Gary''s Student - gsnu200909 Hello, You might want to start with Sumproduct: http://sulprobil.com/html/sumproduct.html But maybe you will end up with a pivot table. ...

Counting characters in a range of cells
Is there a way to count to number of times a given charachter occurs in a range of cells? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ David, If it's a worksheet function you want, try =SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"e",""))) which is an array formula, so enter with Ctrl-Shift-Enter. If you want to do it in VBA, use Debug.Print Application.Evaluate("SUM(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,""e"",""...

stock count log
Is there anyway to print a log for stock counts to account for all stock count documents out on the floor? We do a full yearly inventory and print a count page for every item.We then manually produce a log for those pages to make sure we accout for all of them at the end of the count. Anyway to automate that log? Any inventory suggestions? Thanks ...

Change Font on Character Count
I have a form from the CBOE. The form is used to give responses by students, but has limited space. This worked in Acces 2.0 and Access 97, now it is no longer working! I count the total number of characters on a page. If the count is in certain ranges then I set the font accordingly. Current Code Dim rprt as Report Set rprt = Me With rprt If Me!TextA > 1400 then .fontsize = 13 End If If Me!TextA > 2100 then .fontsize = 12 End I...