| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count occurences from one worksheet to another with "IF" Criteria
Hi Experts: I've done this before but it's been quite a while and I'm having
trouble getting the results I want....This is what I want to do: On Worksheet 1, If Column €œA€ = €œABCD€ and Column €œH€ = €œDummy€, Count Occurrences in Column €œI€ If Greater Than €œ0€ and Enter in Cell €œG4€ on Worksheet 2. Could someone tell me how to do this? Thank you for any help you can provide -- jeannie v |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count occurences from one worksheet to another with "IF" Criteria
Try this:
=SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(ISNUMBER(Sheet1!I1:I10)),--(Sheet1!I1:I100)) If column I will contain only numbers or empty cells then you can eliminate the ISNUMBER test. =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(Sheet1!I1:I100)) -- Biff Microsoft Excel MVP "jeannie v" wrote in message ... Hi Experts: I've done this before but it's been quite a while and I'm having trouble getting the results I want....This is what I want to do: On Worksheet 1, If Column "A" = "ABCD" and Column "H" = "Dummy", Count Occurrences in Column "I" If Greater Than "0" and Enter in Cell "G4" on Worksheet 2. Could someone tell me how to do this? Thank you for any help you can provide -- jeannie v |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count occurences from one worksheet to another with "IF" Crite
Thank you, Biff: This works really good, but I still have the same problem
with it....There are 105 records but this formula only results to 104....There is one record that is a 2 Count and all the other records are a 1 Count....It appears that it is only extracting and counting the 1 Counts....Do you have any suggestions? Thank you for your help, -- jeannie v "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(ISNUMBER(Sheet1!I1:I10)),--(Sheet1!I1:I100)) If column I will contain only numbers or empty cells then you can eliminate the ISNUMBER test. =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(Sheet1!I1:I100)) -- Biff Microsoft Excel MVP "jeannie v" wrote in message ... Hi Experts: I've done this before but it's been quite a while and I'm having trouble getting the results I want....This is what I want to do: On Worksheet 1, If Column "A" = "ABCD" and Column "H" = "Dummy", Count Occurrences in Column "I" If Greater Than "0" and Enter in Cell "G4" on Worksheet 2. Could someone tell me how to do this? Thank you for any help you can provide -- jeannie v |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count occurences from one worksheet to another with "IF" Crite
Hi Biff: My bad.....I think I found the error....It seems to work
perfectly....If I have any other problems, I will post again... Thank you for your expertise.... -- jeannie v "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(ISNUMBER(Sheet1!I1:I10)),--(Sheet1!I1:I100)) If column I will contain only numbers or empty cells then you can eliminate the ISNUMBER test. =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(Sheet1!I1:I100)) -- Biff Microsoft Excel MVP "jeannie v" wrote in message ... Hi Experts: I've done this before but it's been quite a while and I'm having trouble getting the results I want....This is what I want to do: On Worksheet 1, If Column "A" = "ABCD" and Column "H" = "Dummy", Count Occurrences in Column "I" If Greater Than "0" and Enter in Cell "G4" on Worksheet 2. Could someone tell me how to do this? Thank you for any help you can provide -- jeannie v |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count occurences from one worksheet to another with "IF" Crite
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "jeannie v" wrote in message ... Hi Biff: My bad.....I think I found the error....It seems to work perfectly....If I have any other problems, I will post again... Thank you for your expertise.... -- jeannie v "T. Valko" wrote: Try this: =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(ISNUMBER(Sheet1!I1:I10)),--(Sheet1!I1:I100)) If column I will contain only numbers or empty cells then you can eliminate the ISNUMBER test. =SUMPRODUCT(--(Sheet1!A1:A10="ABCD"),--(Sheet1!H1:H10="Dummy"),--(Sheet1!I1:I100)) -- Biff Microsoft Excel MVP "jeannie v" wrote in message ... Hi Experts: I've done this before but it's been quite a while and I'm having trouble getting the results I want....This is what I want to do: On Worksheet 1, If Column "A" = "ABCD" and Column "H" = "Dummy", Count Occurrences in Column "I" If Greater Than "0" and Enter in Cell "G4" on Worksheet 2. Could someone tell me how to do this? Thank you for any help you can provide -- jeannie v |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXCEL allow 2 options on status bar e.g. show "Count" + "Sum" | Excel Discussion (Misc queries) | |||
| Count(if(A3:A200)="100000" if (B3:B200="Y") and (C3:C200=Z))) | Excel Discussion (Misc queries) | |||
| Copy all records matching "Text" criteria to new worksheet | Excel Worksheet Functions | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| Charting number of "name" occurences | Charts and Charting in Excel | |||
Linear Mode
