countif function help

I'm trying to perform a countif function and I was hoping some of th
more excel inclined individuals on this forum could help with this.
What I'm trying to accomplish below is to reference the dates (lef
most column) and then count how many times the same date occurs.  Fo
example, 6/7/2004 has two records.  Then I want to take that number an
look at whether it was a confirm or statement (3rd column), then I wan
to multiply by the amount, but I will have one new column searching fo
just confirms and another new column just for statements.  For example
on 6/7/2004 there was a total of 2 confirms and 2 statements pulled
and for 6/8/2004, there was 1 confirm pulled and 0 statements.  Thi
data will go into a second worksheet that I want to be in the forma
below (after the separating line).

Am I asking too much?  Hopefully someone hear will be able to solve.
Thanks in advance for all your help!  
				
First worksheet				
6/7/2004	Mary-Alice Greco	Confirm	2	Fax
6/7/2004	Mary-Alice Greco	Confirm	2	Fax
6/8/2004	Michele Drella	Confirm	4	Fax
___________________________________________

Second Worksheet
Date            Confirms               Statements
6/7/2004      2                           2
6/8/2004      1                           

--
spiros
-----------------------------------------------------------------------
spirosu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=744
View this thread: http://www.excelforum.com/showthread.php?threadid=27621

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

1 Replies
100 Views

Similar Articles

[PageSpeed] 14

Assuming Sheet2 is set up as follows...


Code
-------------------
    Date	Occurrence	Confirm	    Statement
  6/7/04			
  6/8/04		
-------------------


B2, copied down:

=COUNTIF(Sheet1!$A$2:$A$4,Sheet2!$A2)

C2, copied down and over to the next column:

=SUMPRODUCT(--(Sheet1!$A$2:$A$4=Sheet2!$A2),--(Sheet1!$C$2:$C$4=Sheet2!C$1))

Hope this helps

--
Domeni
-----------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1078
View this thread: http://www.excelforum.com/showthread.php?threadid=27621

0
11/8/2004 3:20:07 PM
Reply:

Similar Artilces:

CountIf Statement
Can anybody help? I am creating a formula in a cell of a spreadsheet which will Count all in stances of "distribution centre" within a column This is achieved using the =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") I was wondering whether it was possible in excel to use a statemen like =COUNTIF('calls closesdon 3 - 4 Dec'!G4:G14, "GROUP") and (nex expression) any ideas -- Message posted from http://www.ExcelForum.com You can use sumproduct to count with more than one condition multiple ranges =SUMPRODUCT(--(Range1="GROUP&...

Help with formula #22
What formula would make this work? Cell A1 has the word Athens Cell C2 has the code ATH (using white text to hide it, as this would be the answer) In cell D2 type the code (correct answer is ATH) If wrong answer typed then it types `wrong` if correct then ATH appears thanks Hi, =IF(D2=C2,D2,"Wrong Answer") You would enter this in E2 for example. A few points, hiding answers using White font is not good, if the cursor is put in the cell the entry still displays on the Formula bar, and if you highlight more than one cell then the white fonted text can be seen through the ...

subtotal function
To all experts, Please could you help me i have a list of people( more than 1 entry for the same person) in column a and and a number in column b i can get excel to do the subtotal function i need to copy the subtotaled data to another part of my spreadsheet but one thing is annoying is that it says A.SMITH SUBTOTAL 999 how do i remove the word subtotal from the cell but still keeping the persons name hope anyone can help thanks in advance steve After you copy and paste Select that column (columns) and do: Edit|Replace what: _subtotal (_ represents a space character)...

Help please with junk e-mails
I have set up outlook 2000 to send unwanted mails to a folder names Junk e mails, I have copied below part of the outlook 2000 help file, can anyone tell me exactly where I can find add in filters as I cannot find them anywhere. You can also filter messages based on a list of e-mail addresses of junk and adult content senders. There are third party filters, which are regularly updated, that you can add to Outlook. These filters have the latest lists of commercial and adult content senders. For more information, see the Outlook Web site at http://www.microsoft.com/outlook. Thanks in advan...

Excel Formulas and Functions: Tax
Hi all, don't know if this is the right place to post this question, o dont know if you will understand what i mean, might be very simple, bu anyway, here goes. I was trying to create a "Pay Calculator" and am having trouble wit the Tax part formula(s). Tax is calculated on value of C10. Exampl is, No tax under $110 made, $1 tax if I make between $110-115, $2 ta if i make between $115-$120, $3 tax if i make between $120-$125 and s on. Just wondering if someone could help me with a solution her hopefully. Please contact me if anymore info is required?. Thanks, Josh llenuts at ...

functions in criteria
The following phrase is in the criteria of a field; Between criteriachange(1,[Begin Date]) And DateAdd("d",daynumber([Begin Date]),criteriachange(1,[Begin Date])) The functions are as follows; Function daynumber(dtedate As Date) As Integer Select Case DatePart("m", dtedate) Case 1 daynumber = 30 Case 2 daynumber = 27 Case 3 daynumber = 30 Case 4 daynumber = 29 Case 5 daynumber = 30 Case 6 daynumber = 29 Case 7 daynumber = 30 Case 8 daynumber = 30 Case 9 ...

IF, COUNTIF
Can someone explain why these two expressions differ referring to the value 5? IF(A2:A10<5 etc. ) but COUNTIF(A2:A10,"<5") requires a comma and quotes Wouldn't it make sense for them to be the same? Just wondering. For us it would seem to make sense, but for the computer not so much :-) My understanding is that in the IF statement the <5 is a Comparison Operation which is a part of the 1st Function Argument whereas in the COUNTIF it is a Criteria Reference supplied as the 2nd independent Function Argument. If it stood alone as simply <5 it would ...

Countif ...
I have a column with phone numbers. I want to count how many phone numbers have the area code "214" and "972". Thanks, the first formula worked. "Jason Morin" wrote: > The formula depends on how your phone numbers are formatted and whether they > are text or actual 10 digit numbers. For example, if they are text and you > simply need the first 3 numbers in the cell, try: > > =SUMPRODUCT(--(LEFT(A1:A10,3)={"214","972"})) > > If the area codes are enclosed in parentheses, you could use: > > =SUM(COUNTIF(A1:A10...

Criteria/CountIf and Pivot Table
Hello: My data looks like this ID Date CWA Amount 1 08/12/2006 0 $0.00 2 08/13/2006 1 $10.00 3 08/14/2006 1 $20.00 4 08/01/2006 0 $0.00 Based on the above data, I want to create a pivot, by month (I know I only have Aug here) to show the following: Sum of CWA Count of CWA Percent of cases that were submitted with CWA. The CWA is an indicator field that looks at another field (not listed here) to determine if there is cash in a...

axis label multi line help
hello how do you create multi line axis labeling. I have a bar graph with text angled at 45 degrees under each bar. I would like to set the text so that it is displayed on 2 lines. I tried entering a carraige return (ALT ENTER) in the reference cell but it didnt work. sincerely, sp Hi SP have a look at http://peltiertech.com/Excel/Charts/Staggered.html HTH Frank SP wrote: > hello > how do you create multi line axis labeling. > > I have a bar graph with text angled at 45 degrees under each bar. > I would like to set the text so that it is displayed on 2 lines. > I tri...

How to use function strcpy to copy unsigned char?
Hello all: Look: unsigned char shellcode[]="\xC6\x45\xF7\x30\xB8\xBF\x8E\x01"; char buffer[20]; strcpy(buffer,code); I must use the function strcpy,who could tell me how to do?Thanks very much. Lee Tow wrote: > Hello all: > Look: > unsigned char shellcode[]="\xC6\x45\xF7\x30\xB8\xBF\x8E\x01"; > char buffer[20]; > strcpy(buffer,code); > I must use the function strcpy,who could tell me how to do?Thanks very > much. > > CStdString is probably the best at who cares what kind of string it is. "Lee Tow" <...

Countif
I am trying to solve the following problem i) cells A1 to A10 have either M or F (male or female) ii) cells B1 to B10 have either (grades) A B or C iii) I want (eg cell B13) to state the number of female students who scored A... and cell B14; how many female students who gained B etc I have tried various things including countif, sumif etc. It seems like a straight forward problem but the solution evades me! Eddie =SUMPRODUCT((A1:A10="F")*(B1:B10=LEFT(ADDRESS(ROW(B1),ROW(A1),4),1))) in Cell B13 and filled down to Cell B15. Alan Beban Eddie wrote: > I am trying to solve ...

Help! cleaning out inbox and saved emails
I have created in the past two years files in my inbox but want to delete them as they are taking up too much space. How do I transfer these email from my inbox file to a file(s) in Microsoft word. The only way I have found to date is to (Save AS) save each one individually to a word file but this is way too time consumming. Help please. Thanks so much BB Do they have to be in Word? You can create a folder on the Desktop and just drag the messages into it. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "BB" <BB@discussions.micr...

General Help
Hey, with the world cup draw just gone, i was trying to make a sprea sheet, one page tables, 2nd page results, and all u do is fill in th reults, and then it fills in the table automatic, i managed to do th formula's for, GF (simple, 3 cells added) GA (3 cells added again) an GD (= GF - GA) but i don't know how to do, Games Won, Games Drawn, Games Lost, Points, and for Games played i did, a 1, or 0 next to each fixture, (so i jus add a 1 when the game has been played - add all that team's together get games played) [is there another way for that one??] any help? -- M4dSk1ll --...

Formula help #56
Hi Folks, Thanks for being here. I'm just learning Excel 2007 and hope someone here can give me a formula that will solve my problem.. A1 = $12445.54 (Input value) B1 = $4978.22 (result of simple formula =sum(C1*D1)) Problem: Do not want B1 to calculate beyond the value in A1 regardless of values in C1 or D1. Can anyone steer me in the right direction ? TIA MamaBee In article <4c8e58d5$0$11813$9a566e8b@news.aliant.net>, MamaBee@ns.sympatico.ca says... > > Hi Folks, > Thanks for being here. > I'm just learning Excel 2007 and hope someone here can give me ...

RPC/HTTPS functionality in Entourage 2008
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Email Client: Exchange Hi All, <br><br>I recall that a similiar feature is working in entourage 2004 and I have managed to find several guides on the net for this configuration. <br> However, I am facing challenges to configure this in Entourage 2008 and have failed to find any articles that provides the configuration steps for Entourage 2008 connectivity to Exchange from home. <br><br>Question is this feature still available? <br> Can someone please point me to the correct direction. Many thanks...

Excel countif and
is there a "and" condition if you use countif or sumif? example; 1 c 1 1 c 2 2 < countif(b1:b4,"=c")and(a1:a4,"=1") Hi! Try this: =SUMPRODUCT(--(A1:A4=1),--(B1:B4="C")) Biff "KEN" <KEN@discussions.microsoft.com> wrote in message news:02a801c54ae1$3371a4f0$a401280a@phx.gbl... > is there a "and" condition if you use countif or sumif? > example; > > > 1 c > 1 > 1 c > 2 > 2 < countif(b1:b4,"=c")and(a1:a4,"=1") > ...

Mulit Function Query
I am working on this query. I want to run it, so it will show what my users are missing in their files. But only what they are missing. What would the criteria be in order to do that? On Tue, 6 Apr 2010 09:56:21 -0700, LewisDUA <LewisDUA@discussions.microsoft.com> wrote: >I am working on this query. I want to run it, so it will show what my users >are missing in their files. But only what they are missing. What would the >criteria be in order to do that? You'll have to give us some more explanation. I know that one thing that's missing from my files i...

formula help #44
Good day, I am a lecturer and would like help with the following problem : I have 10 students in a classs and the pass mark is 50% I want to determine the no failed i.e how many students got below 50% Test Marks stud 1 65 stud 2 50 stud 3 23 stud 4 90 stud 5 15 stud 6 88 stud 7 35 stud 8 44 stud 9 39 stud 10 95 no failed 5 how can this be done?Is there a function I can use? Thanking you Cheers =COUNTIF(B:B,"<" & 50) -- Gary''s Student - gsnu200787 Unless there i...

CountIf with And
Novice Excel user on Excel 2003. I have a yearly data input sheet to track codes. I use the date (fomatted as 1/01/2009) and a code. I want to track the codes in a mothly chart for ease in summarization and improvment tracking. The codes are used to define a department and an error in that department (code 1a means - AR department and error a). I need to pull and total the amount for each code for each month. After searching for a while it seems that using CountIf with And does not work. Any suggestions would be greatly appreciated. -- Mike =SUMPRODUCT(--(MONTH(date_range)=4),...

HELP 05-23-10
Can I send e-m ail to all address? It seems charter.net is not going thru? What do you mean? You can send email to any address you want. Or perhaps you are talking about sending the same message to multiple addresses (note the plural). Your mail provider (Charter) will have a limit on the number of recipients per message, typically 50-100. If you need to know the exact limit, give Charter a call. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "Faye" <faye68@yhti.net> wrote in message = news:OthrUPs%23KHA.397...

Finance Charge HELP!!!
We use RMS SO. Our charge accounts are due the 10th of the month. When we run statements every month the people who paid their account on the 10th are still having finance charges applied to their statements. Our billing cycles opening/closing dates are the first day and last day of the month. Are we doing something wrong?? You would guess if its due on the 10th and you pay on the 10th you would not aquire finance charges. RMS associates the payment with the day of or after in a different billing cycle. The only way I know to fix those particular accounts is to manually remove the Fin...

Inserting a cell value from an "IF" function
I want to do something that confuses me to explain, but I'll give it a go. I need to be able to insert a name from one worksheet onto another, depending on a letter in another column alongside the name. I need to be able to do this for several names in a list. I wonder if this is possible as I have no real idea where to start :) Thanks! -- -Liam >I have no real idea where to start :) Start here: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "liamellis91" <liamellis91@discussions.microsoft.com> wrote in message...

rank function
In my spreadsheet cells a1 through a6 are: 1 5 12 0 6 0 These numbers are calculated, not input. I want to rank these (in ascending order) and ignore all cells containing zero(s). The normal rank command [=RANK(cell,$a$1:$a$6,1) ] gives this result as I would expect: 3 5 6 1 5 1 What can I do to get this result? 1 2 4 3 I am using Excel 2000. Thanks in advance. Robert Try =IF(A1=0,"",SUMPRODUCT(--($A$1:$A$6>0),($A$1:$A$6<=$A1)*1)) change ranges to suit. Peter "Robert Dieckmann" wrote: > In my spreadsheet cells a1 through a6 are: > 1 > 5 > 12...

Countif() formula
Hi I have a column in Excel, D7:D200, filled of real numbers. Also the cell F3 includes a number (variable cell). I would like to count how many numbers of the column D7:D200 are >F3, but in successive ranges of D column. (That means how many numbers in: D7:D8 > F3, D7:D8 > F3, D7:D9 > F3…………….and finally D7:D200 > F3). To do that I have applied in E7 the formula: Countif(D7:$D$7 ; “>F3”) and expand down to E200. Unfortunately the above formula returns zero in all applied cells. If I change the F3 in the formula using a stable number (2,3,4 etc) the formula works, but...