| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Criteria, Count If, Sum Product to get count across range
Hi all,
Pseudo-Follow-Up to my last question: Going based on the below example, how can I write a formula that will give me a count of the total lines that meet this criteria: Name--Dog, Male--Y, Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case below, the count/value showing up in the cell with the formula I want would be (if I can count correctly) 2. Eliminating the last field, I would've used SumProduct for all of these, but now that I'm breaking it down further to include specific ID numbers, I'm not sure what the best and efficient way I can do it is. I'm sure I could do SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that would just be a large hassle and I wondered if there's a smaller formula. Thanks! Name Male Brown Size ID# Dog Y Y Big 1 Dog N Y Big 2 Dog Y Y Small 3 Cat Y Y Small 4 Cat Y Y Big 5 Dog Y N Big 6 Dog Y Y Big 7 Cat Y Y Big 8 Cat Y Y Medium 9 Apple N Y Small 10 Banana N Y Small 11 Apple N Y Small 12 Apple N Y Big 13 Apple N Y Big 14 Dog N N Big 15 Dog Y N Small 16 Dog Y Y Big 17 Banana N N Medium 18 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criteria, Count If, Sum Product to get count across range
This yields a result of 3:
=SUMPRODUCT(--(A1:A19="Dog"),--(B1:B19="Y"),--(C1:C19="Y"),--(D1:D19="Big")) Regards, Ryan--- -- RyGuy "Jonathan" wrote: Hi all, Pseudo-Follow-Up to my last question: Going based on the below example, how can I write a formula that will give me a count of the total lines that meet this criteria: Name--Dog, Male--Y, Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case below, the count/value showing up in the cell with the formula I want would be (if I can count correctly) 2. Eliminating the last field, I would've used SumProduct for all of these, but now that I'm breaking it down further to include specific ID numbers, I'm not sure what the best and efficient way I can do it is. I'm sure I could do SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that would just be a large hassle and I wondered if there's a smaller formula. Thanks! Name Male Brown Size ID# Dog Y Y Big 1 Dog N Y Big 2 Dog Y Y Small 3 Cat Y Y Small 4 Cat Y Y Big 5 Dog Y N Big 6 Dog Y Y Big 7 Cat Y Y Big 8 Cat Y Y Medium 9 Apple N Y Small 10 Banana N Y Small 11 Apple N Y Small 12 Apple N Y Big 13 Apple N Y Big 14 Dog N N Big 15 Dog Y N Small 16 Dog Y Y Big 17 Banana N N Medium 18 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criteria, Count If, Sum Product to get count across r
But that doesn't factor in the ID#
"ryguy7272" wrote: This yields a result of 3: =SUMPRODUCT(--(A1:A19="Dog"),--(B1:B19="Y"),--(C1:C19="Y"),--(D1:D19="Big")) Regards, Ryan--- -- RyGuy "Jonathan" wrote: Hi all, Pseudo-Follow-Up to my last question: Going based on the below example, how can I write a formula that will give me a count of the total lines that meet this criteria: Name--Dog, Male--Y, Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case below, the count/value showing up in the cell with the formula I want would be (if I can count correctly) 2. Eliminating the last field, I would've used SumProduct for all of these, but now that I'm breaking it down further to include specific ID numbers, I'm not sure what the best and efficient way I can do it is. I'm sure I could do SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that would just be a large hassle and I wondered if there's a smaller formula. Thanks! Name Male Brown Size ID# Dog Y Y Big 1 Dog N Y Big 2 Dog Y Y Small 3 Cat Y Y Small 4 Cat Y Y Big 5 Dog Y N Big 6 Dog Y Y Big 7 Cat Y Y Big 8 Cat Y Y Medium 9 Apple N Y Small 10 Banana N Y Small 11 Apple N Y Small 12 Apple N Y Big 13 Apple N Y Big 14 Dog N N Big 15 Dog Y N Small 16 Dog Y Y Big 17 Banana N N Medium 18 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criteria, Count If, Sum Product to get count across range
Try this:
=SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{1,2,3,5,7,8,15,16},0)))) -- Biff Microsoft Excel MVP "Jonathan" wrote in message ... Hi all, Pseudo-Follow-Up to my last question: Going based on the below example, how can I write a formula that will give me a count of the total lines that meet this criteria: Name--Dog, Male--Y, Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case below, the count/value showing up in the cell with the formula I want would be (if I can count correctly) 2. Eliminating the last field, I would've used SumProduct for all of these, but now that I'm breaking it down further to include specific ID numbers, I'm not sure what the best and efficient way I can do it is. I'm sure I could do SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that would just be a large hassle and I wondered if there's a smaller formula. Thanks! Name Male Brown Size ID# Dog Y Y Big 1 Dog N Y Big 2 Dog Y Y Small 3 Cat Y Y Small 4 Cat Y Y Big 5 Dog Y N Big 6 Dog Y Y Big 7 Cat Y Y Big 8 Cat Y Y Medium 9 Apple N Y Small 10 Banana N Y Small 11 Apple N Y Small 12 Apple N Y Big 13 Apple N Y Big 14 Dog N N Big 15 Dog Y N Small 16 Dog Y Y Big 17 Banana N N Medium 18 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criteria, Count If, Sum Product to get count across r
Thanks--what if the ID# includes letters?
"T. Valko" wrote: Try this: =SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{1,2,3,5,7,8,15,16},0)))) -- Biff Microsoft Excel MVP "Jonathan" wrote in message ... Hi all, Pseudo-Follow-Up to my last question: Going based on the below example, how can I write a formula that will give me a count of the total lines that meet this criteria: Name--Dog, Male--Y, Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case below, the count/value showing up in the cell with the formula I want would be (if I can count correctly) 2. Eliminating the last field, I would've used SumProduct for all of these, but now that I'm breaking it down further to include specific ID numbers, I'm not sure what the best and efficient way I can do it is. I'm sure I could do SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that would just be a large hassle and I wondered if there's a smaller formula. Thanks! Name Male Brown Size ID# Dog Y Y Big 1 Dog N Y Big 2 Dog Y Y Small 3 Cat Y Y Small 4 Cat Y Y Big 5 Dog Y N Big 6 Dog Y Y Big 7 Cat Y Y Big 8 Cat Y Y Medium 9 Apple N Y Small 10 Banana N Y Small 11 Apple N Y Small 12 Apple N Y Big 13 Apple N Y Big 14 Dog N N Big 15 Dog Y N Small 16 Dog Y Y Big 17 Banana N N Medium 18 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple Criteria, Count If, Sum Product to get count across r
Doesn't make a difference except in the way you enter them in the MATCH
function: =SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{"A21E","B","200F","3H",10},0)) )) If the ID contains letters then those are TEXT values. Text values need to be enclosed in double quote: "A21E". Numeric values do not need to be enclosed in quotes: 10 -- Biff Microsoft Excel MVP "Jonathan" wrote in message ... Thanks--what if the ID# includes letters? "T. Valko" wrote: Try this: =SUMPRODUCT(--(name="dog"),--(male="y"),--(brown="y"),--(size="big"),--(ISNUMBER(MATCH(ID,{1,2,3,5,7,8,15,16},0)))) -- Biff Microsoft Excel MVP "Jonathan" wrote in message ... Hi all, Pseudo-Follow-Up to my last question: Going based on the below example, how can I write a formula that will give me a count of the total lines that meet this criteria: Name--Dog, Male--Y, Brown--Y, Size--Big, ID # only 1, 2, 3, 5, 7, 8, 15, 16. So, in the case below, the count/value showing up in the cell with the formula I want would be (if I can count correctly) 2. Eliminating the last field, I would've used SumProduct for all of these, but now that I'm breaking it down further to include specific ID numbers, I'm not sure what the best and efficient way I can do it is. I'm sure I could do SumProduct(Name)*(Male)*(Brown)*Size*(ID#_) + SumProduct...*ID#_ but that would just be a large hassle and I wondered if there's a smaller formula. Thanks! Name Male Brown Size ID# Dog Y Y Big 1 Dog N Y Big 2 Dog Y Y Small 3 Cat Y Y Small 4 Cat Y Y Big 5 Dog Y N Big 6 Dog Y Y Big 7 Cat Y Y Big 8 Cat Y Y Medium 9 Apple N Y Small 10 Banana N Y Small 11 Apple N Y Small 12 Apple N Y Big 13 Apple N Y Big 14 Dog N N Big 15 Dog Y N Small 16 Dog Y Y Big 17 Banana N N Medium 18 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count a range of number to a given parameter or criteria. | Excel Worksheet Functions | |||
| Count unique numbers in a range with a given criteria | Excel Discussion (Misc queries) | |||
| count criteria within a set range in excel | Excel Discussion (Misc queries) | |||
| Count Multiple Criteria | Excel Worksheet Functions | |||
| How do I count data in range A:A that is dependent upon criteria . | Excel Worksheet Functions | |||
Linear Mode
