Formula needed 01-22-10

Hi
I need a formula that will look in one column range for a certain value and 
for another value in another column range and when they are both found in the 
same row, will count the number of occurances. Help!!!!
0
Utf
1/22/2010 3:31:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
642 Views

Similar Articles

[PageSpeed] 39

Krissy wrote:
> Hi
> I need a formula that will look in one column range for a certain value and 
> for another value in another column range and when they are both found in the 
> same row, will count the number of occurances. Help!!!!


http://www.contextures.com/xlFunctions01.html#SumProduct

Omit the "values to be summed".

=SUMPRODUCT(--(A2:A6="Active"), --(B2:B6>=10))
0
Glenn
1/22/2010 3:40:11 PM
Glenn wrote:
> Krissy wrote:
>> Hi
>> I need a formula that will look in one column range for a certain 
>> value and for another value in another column range and when they are 
>> both found in the same row, will count the number of occurances. Help!!!!
> 
> 
> http://www.contextures.com/xlFunctions01.html#SumProduct
> 
> Omit the "values to be summed".
> 
> =SUMPRODUCT(--(A2:A6="Active"), --(B2:B6>=10))


More specifically...

http://www.contextures.com/xlFunctions04.html#SumProduct
0
Glenn
1/22/2010 3:52:12 PM
Say In A1 thru B10 we have:

Krissy	Sad
Krissy	Sad
Krissy	Sad
Krissy	Happy
Krissy	Happy
Debbi	Happy
Debbi	Happy
Debbi	Sad
Debbi	Sad
Debbi	Sad

and we want to count the rows in which Krissy is Happy:

=SUMPRODUCT((A1:A10="Krissy")*(B1:B10="Happy"))
-- 
Gary''s Student - gsnu200909


"Krissy" wrote:

> Hi
> I need a formula that will look in one column range for a certain value and 
> for another value in another column range and when they are both found in the 
> same row, will count the number of occurances. Help!!!!
0
Utf
1/22/2010 3:54:01 PM
I have tried both suggestions and they don't seem to work right. More info 
may help:
 In Column E Row 3 to 57 are various numbers - 1 through to 112 in column G 
row 3 to 57 are either a T or F
 I need to count how many times a number such as "14" in column E has a Y in 
column G on the same row. i.e. Row 51 Column E = 14 column G = Y so would be 
counted as 1 and if row 46 had the same criteria I would then have a count of 
2 and so on.

"Krissy" wrote:

> Hi
> I need a formula that will look in one column range for a certain value and 
> for another value in another column range and when they are both found in the 
> same row, will count the number of occurances. Help!!!!
0
Utf
1/22/2010 4:30:10 PM
=sumproduct(--(e3:e57=14),--(g3:g57="Y"))

Regards,
Fred

"Krissy" <Krissy@discussions.microsoft.com> wrote in message 
news:C5710F39-709E-43B1-B522-3EE51B06B3C9@microsoft.com...
>I have tried both suggestions and they don't seem to work right. More info
> may help:
> In Column E Row 3 to 57 are various numbers - 1 through to 112 in column G
> row 3 to 57 are either a T or F
> I need to count how many times a number such as "14" in column E has a Y 
> in
> column G on the same row. i.e. Row 51 Column E = 14 column G = Y so would 
> be
> counted as 1 and if row 46 had the same criteria I would then have a count 
> of
> 2 and so on.
>
> "Krissy" wrote:
>
>> Hi
>> I need a formula that will look in one column range for a certain value 
>> and
>> for another value in another column range and when they are both found in 
>> the
>> same row, will count the number of occurances. Help!!!! 

0
Fred
1/22/2010 4:55:00 PM
"Krissy" wrote:
> I have tried both suggestions and they don't seem to work right.

They should have.  How exactly how did you apply them?  That is, exactly 
what formula are you trying in our worksheet that does not work? 


> I need to count how many times a number such as
> "14" in column E has a Y in column G on the same row.

Ostensibly:

=sumproduct((E3:E57=14)*(G3:G57="Y"))

But if the "numbers" in column E are actually text, then:

=sumproduct((E3:E57="14")*(G3:G57="Y"))

Note:  The comparision G3:G57="Y" will be true when each of G3:G57 is either 
"Y" or "y".  Is that a problem?


----- original message -----

"Krissy" wrote:
> I have tried both suggestions and they don't seem to work right. More info 
> may help:
>  In Column E Row 3 to 57 are various numbers - 1 through to 112 in column G 
> row 3 to 57 are either a T or F
>  I need to count how many times a number such as "14" in column E has a Y in 
> column G on the same row. i.e. Row 51 Column E = 14 column G = Y so would be 
> counted as 1 and if row 46 had the same criteria I would then have a count of 
> 2 and so on.
> 
> "Krissy" wrote:
> 
> > Hi
> > I need a formula that will look in one column range for a certain value and 
> > for another value in another column range and when they are both found in the 
> > same row, will count the number of occurances. Help!!!!
0
Utf
1/22/2010 4:56:01 PM
Reply:

Similar Artilces:

Clear Contents But Not Formula
I want to use a complicated worksheet that was devised for last year but contents will now change to this year. How can I clear the contents of the cells, the numbers, but leave the formula remaining? Hi click in the sheet you want to do this in (please try on a copy of your workbook first), choose edit / goto, click the special button and then check "constants" .. there's some tick boxes you can play with too ... then click OK and press the delete key. Hope this helps Cheers JulieD "Gancom3" <Gancom3@discussions.microsoft.com> wrote in message news:8749...

How do you add text after a formula?
I'm working on creating a report. At the top of each section is a mont that I type in. At the bottom I want a cell to display that month an also add the word "Total". So for example I have May listed in Cell B3 I now want "May Total" to be listed in cell B36. I know if I want it t display just "May" I'd enter =B3 but how do I make it add the Tota part? Thanks in advance for any help -- Wease ----------------------------------------------------------------------- Weasel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2720 Vie...

Dates in Formulae
Dear all, Suppose I want to create a basic formula eg one which adds 6 months to a date, how would I do it? Doing it by date + 6 months worth of days will obviously not work as the number of days in 6 months will vary. Any thoughts? Thanks, Danny Hi Danny, =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) But do think about what you want the day to be in case the source date is, for example, august 31. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Aardvark" <djurmann@hotmail.com> wrote in message news:%23cU8IWF0DHA.2580@TK2MSFTNGP09.phx.gbl... > Dear all, > > ...

Formulas for race game (position calculation after each round)
Hi. I have a worksheet like the following: Player's Name-----Round1(R1)-----R2-----R3...-----Total Score====Position after R1-----Pos.(R2)-----Pos.(R3)... Position is calculated based on the score accumalted (the more scores, the better) so far. Eg: Postion after R1 depends on score got in R1. Position after R2 depends on score got in R1 & R2. Position after R3 depends on score got in R1 & R2 & R3. Q: What formulas should I use to calculate the positions up to different rounds? I think I can use RANK functions, but it seems I need to set SUM formulas to calculate total scores...

Need message box to be at front of screen
Greetings all. I have set a timer on a form which has a message box that pops up at defined intervals telling the user they are still being timed. The problem is that if they are using another application with access minimized, the message box is not visible until they minimize all other applications that are on top of it. Is there a way to make the message box show on top of all other open applications? I am using Access 2003 with XP Pro. Thank you. -- Message posted via http://www.accessmonster.com If you are using the Access application to pop up an Access messagebox, I ...

Formula for calculating Gross Profit with muktiple discounts #2
List Price less Disc1 less Disc2 equals Net Price. Sell price less net price equal profit divided by sell price equal gross margin percentage. $10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less $4.50 =$1.50. $1.50/6.00 = 25% GP. All of these figures are in cells of their own. Cannot get this to calculate correcly. Please help! Urgent CARM Wrote: > List Price less Disc1 less Disc2 equals Net Price. Sell price less net > price equal profit divided by sell price equal gross margi > percentage. > > $10 less 50% less 10% =$4.50. Sell for $12.50. $6.00 less > $4....

Date Changes to 01/00/00 when i enter any date.
Using XL2003, I have a problem. When i enter a date in cell, such as 08/25/10, or use the shortcut Ctrl ;, XL displays 01/00/00 in the cell instead. I have formatted the cell as a date. What can I do to fix this problem? Thank you so much. Tonso ...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! ...

Pivot Table Sort 03-05-10
Hi I'm trying to sort a pivot table but it'll only let me sort on the first column, I need to sort it on the third, please can anyone help? Hi You will need to drag your 3rd Field to the 1st position in the row Area of the PT. -- Regards Roger Govier jen2018 wrote: > Hi > > I'm trying to sort a pivot table but it'll only let me sort on the first > column, I need to sort it on the third, please can anyone help? ...

Excel Formula Error for No Good Reason
I don't know why, but this formula will not stop giving me an error in Excel 2003. =IF( $A8 <> "", B8 * E8, IF( ROW(H7) <> 1, IF( G8 = "Subtotal", SUM( H$2:H7 ), IF( LEFT(G8, 3) = "Tax", ROUND( $J$1 * H7, 2), IF( G8 = "Total", INDIRECT( ADDRESS( ROW(H8)-2, COLUMN(H8) ) ) + H7, IF( G8 = "Depr", SUM( INDIRECT( "G2:"&ADDRESS( ROW(H8) - 4, COLUMN(G7),4) ,TRUE) ), "" ) ...

CRM REPORTS 01-18-07
Hi everyone. I've created reports using Reporting Services. I then imported these reports into CRM as part of a CRM development for a client. The client accesses our CRM website through the internet, using an ip address. They are then prompted for their credentials, which they then enter. They can successfully enter CRM, but cannot access our custom reports, however, they can access the standard CRM reports. I've successfully accessed CRM and the reports via the internet, using their credentials. Basically, I can access everything from my side, but they can only access CRM and ...

excel formula.
=IF(Sheet1!B4=Sheet1!B1,2,If(Sheet1!B4>Sheet1!B1,1,If (Sheet1!B4<Sheet1!B1,4) In the above formula how would I insert an incremental amount. ie: If(Sheet1!B4>Sheet1!B1,1, the increase to be by two and the result remain one. ie: (Sheet1!B4<Sheet1!B1,4) the decrease be by 1 with the result remaining 4 I am sure that what you want to do is possible, but you have to be a little more descriptive to make us understand. Thanks. Ideally, put up some cell names, put values, then say what you want done. Boris >-----Original Message----- >=IF(Sheet1!B4=Sheet1!...

formula
Hi, I want to copy A1, A3, A5, A7 etc. into a seperate column, but when I try to copy it down, it doesn't seem to recognise my odd numbers request. What formula should I use? Thanks! Use =INDIRECT("A"&ROW(A1)*2-1) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Georgyneedshelp" <Georgyneedshelp@discussions.microsoft.com> wrote in message news:464A8951-DB29-4907-AE09-AA7CE4F0C15A@microsoft.com... > Hi, > > I want to copy A1, A3, A5, A7 etc. into a seperate column, but when I try to > copy it down, it doe...

Vista 10-25-07
Is there any REALLY good reason to upgrade from Windows Vista Home Premium to the Windows Vista Ultimate? -- Eykleidis Wrong news group, but that depends strictly on what you want to do in Vista. I personally will not use anything less than Ultimate, but as a developer that is what my requirements are. I also have an MSDN subscription that allows me to install the "flavour" I may need for testing., -- The personal opinion of Gary G. Little "Eykleidis" <eykleidis21@yahoo.gr> wrote in message news:Ojb7jRrFIHA.5208@TK2MSFTNGP04.phx.gbl... > Is there any...

Formula Syntax
Hi I am using the formula below to bring data from one sheet to another. =SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data! $P$3:$P$5017="Tricia Woodgate"),Data!$Q$3:$Q$5017) However at the end I want to return the sum of Q3+R3:Q5017+R5017. Can someone tell me how to set up the syntax please Thanks A "Alex Hammerstein" <aph@misnet.co.uk> wrote: > Hi I am using the formula below [....]. > > =SUMPRODUCT(--(TEXT(Data!$O$3:$O$5017,"mmm-yy")=TEXT($A9,"mmm-yy")),--(Data! > $P$3:$P$5017=&q...

Text box validation 01-04-08
I have a form with several text boxes that I want to check for duplicate values in the table. The PK inthe table is an Autonumber. What I want to do is not allow the FirstName LastName FileNo and SSN to be the same for any two records. The combination of these fields would garuntee that the same individual would not be allowed to have two records created. You could set a unique index in the underlying table on those four fields. This would disallow the entry of duplicates (but only after the fact -- after the user entered values and tried to save them). Another approach would be to g...

Changing formula in multiple cells or range simultaneously
I am trying to change the value in multiple cells in a large worksheet simultaneuously. I want to identify the range and then adjust the formula in the entire range. Is there a way that I can highlight the range and then change to formula in each, simultaneously? For example, if I wanted to double the value in the entire range, how would I do this? Thanks, Michael You could put 2 in an empty cell. Copy it Edit|Paste special|click on Multiply under the operation section. Then clear out that 2. But it really depends on what kind of change you're making. If you wanted to ad...

i need your help
i am providing you with my problem with an excel attachment file please reply me asap. Attachment filename: help me.xls Download attachment: http://www.excelforum.com/attachment.php?postid=463626 --- Message posted from http://www.ExcelForum.com/ Hi I would suggest using Data / Filter / AutoFilter. This is a very simple yet effective way of doing what you want. If you need to create separate 'tables' you can use the Advanced Filter Andy. "mangesh >" <<mangesh.12lfkr@excelforum-nospam.com> wrote in message news:mangesh.12lfkr@e...

Need Help #9
Hi This =IF(H2,MIN(10,H2),0) returns the numbers from column H with a maximum of 10. The formula is returning 10 if the cell is 10 or over, is something wrong. Question: I need it to return 0 if 10 or over. Microsoft Excel 2002 Thanks in advance Dave On Mon, 14 Nov 2005 16:43:49 -0000, "Dave 2005" <dave@zillion.karoo.co.uk> wrote: >Hi > >This =IF(H2,MIN(10,H2),0) returns the numbers from column H with a maximum >of 10. > >The formula is returning 10 if the cell is 10 or over, is something wrong. > >Question: I need it to return 0 if 10 or ...

Small Business Server 07-10-03
I want to set up a new network for only about 10-15 users by implementing Small Business Server. I am just wandering from an IT perspective...How difficult will it be? Seems like an odd question for the CRM NG. I answered your question in the SBS NG. Brandon >-----Original Message----- >I want to set up a new network for only about 10-15 users >by implementing Small Business Server. I am just >wandering from an IT perspective...How difficult will it >be? >. > Its pretty straightforward. The install for SBS is pretty easy to follow. "Jon" <jonk...

Excel Array Formula: Multiple Criteria Sum IF Challenge
Currently, I have the following Excel Worksheet Invc No Code Status Charges RejCode 291 CH no pay 50 291 CH no pay 50 291 PY no pay ded 152 CH no pay 50 152 CH no pay 25 152 PY no pay dat 206 CH no pay 50 206 CH no pay 50 206 PY no pay 507 CH no pay 50 507 CH no pay 45 507 PY no pay ded 600 CH overpaid 25 600 CH overpaid 25 600 PY overpaid ded I would like to obtain the following results, Total Charges by Rejecte "no pay" invoices and the specific "no pay" invoices with rejections a displayed below: Total Charges by Rejected "No pay"...

Need a little Macro ...
I need a little Macro that will race thru my Tab Sheets regardless of Tab Sheet Name ... Active sheet ... perform a function ... next sheet ... until all functions complete ... all sheets ... Thanks ... Kha Ken, Try something like Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets ' do something with WS Next WS -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Ken" <anonymous@discussions.microsoft.com> wrote in message news:017001c394da$72b82d00$a401280a@phx.gbl... > I need a l...

Need to set up ID on Outlook Express
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=56dc2f64-f688-4919-a5ea-23b1a55eb054&dg=microsoft.public.outlook Try posting this in an Outlook Express news group - this is not one of them. Outlook i...

Need smart guy advice! VIRUS !?
"Hi. I recently installed SP3 on my computer, and now I get an odd message when I open emails. A box pops up that says, "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose 'No'." My virus scan turned up nothing, and the "detect & repair" function of Outlook did not change this." "After 'Yes' or 'No' outlook stops responding." Thanks, Max. Assuming you are describing Outlook 2002 (part of Office XP), ...

Resolve Case, Resolution Type Field (Need more options)
I need to add more options to the Resolution Type Field (There is only the PROBLEM SOLVED option) and i can't find where , It seems to be a Pickup list but the form Resolve Case and its fields do not appear anywhere, Please let me know how can i do this if somebody knows it.... -- cB - Thanks in advance I'm having the same issue! "cB" wrote: > I need to add more options to the Resolution Type Field (There is only the > PROBLEM SOLVED option) and i can't find where , It seems to be a Pickup list > but the form Resolve Case and its fields do not appear...