| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM, COUNT and SUMPRODUCT
The following formulae yield the same results.
=SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
Try this and see if you can figure out why it works. (just trying to "push"
you to think) (don't use Evaluate Formula until you've given up) array entered =COUNT(IF((A1:A10="A")*(E1:E10="J"),100)) Biff "Epinn" wrote in message ... The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Epinn" wrote: The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
Yes, I spent hours studying Bob's (my teacher's) paper weeks ago. For your information, I am a fan of SUMPRODUCT, but I have to learn not to be too attached to it. Long story ...... Bob and Roger know why.
Thanks. Epinn "Jim Thomlinson" wrote in message ... Check out this link... http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH... Jim Thomlinson "Epinn" wrote: The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
=COUNT(IF((A1:A100="a")*(E1:E100="J"),1))
Array formula CtrlShiftEnter "Epinn" wrote: The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
Anyone wants to hear a joke? It's on me. I was stuck when I tried to come up with a formula using COUNT( ). Why? I thought I had to include an array in the formula for COUNT( ) to count and I couldn't figure out ....... I forgot that *values* are okay too. I tried to reference some COUNT( ) examples. Believe it or not, all of them have an array. There are scenarios that count without using an array but they use SUM( ) and the value "1." As a result, I am convinced that I need an array and I am more stuck. Today, I finally find an example of COUNT( ) counting value. Guess what, it is not purely counting value; it still counts an array *and* a value.
Why are users more *inclined* to use SUM and 1 instead of COUNT and 1? Is there some kind of a "secret" that I am not aware of? :) Biff, you are such a good teacher; you don't spoon-feed. Unfortunately, in this case, it's not so much about me not "thinking," it is a case of me not "remembering correctly" *and* am convinced by the examples that my theory is correct. You brought up a very good point. It doesn't make any difference if we use 1 or 100 when we use COUNT( ). But I think 100 can easily trick us (at least me) into thinking SUM ( ) even when COUNT( ) is staring at us. I have no problem with the Boolean though. This thread has helped me see COUNT, SUM and SUMPRODUCT as one big picture. I hope I can easily jump from one function to another in the future. I can't do it without the help from the group. Appreciated. Epinn "Biff" wrote in message ... Try this and see if you can figure out why it works. (just trying to "push" you to think) (don't use Evaluate Formula until you've given up) array entered =COUNT(IF((A1:A10="A")*(E1:E10="J"),100)) Biff "Epinn" wrote in message ... The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
It doesn't make any difference if we use 1 or 100 when we use COUNT( ).
But I think 100 can easily trick us (at least me) into thinking SUM ( ) even when COUNT( ) is staring at us. That was my intention in using 100. Biff "Epinn" wrote in message ... Anyone wants to hear a joke? It's on me. I was stuck when I tried to come up with a formula using COUNT( ). Why? I thought I had to include an array in the formula for COUNT( ) to count and I couldn't figure out ...... I forgot that *values* are okay too. I tried to reference some COUNT( ) examples. Believe it or not, all of them have an array. There are scenarios that count without using an array but they use SUM( ) and the value "1." As a result, I am convinced that I need an array and I am more stuck. Today, I finally find an example of COUNT( ) counting value. Guess what, it is not purely counting value; it still counts an array *and* a value. Why are users more *inclined* to use SUM and 1 instead of COUNT and 1? Is there some kind of a "secret" that I am not aware of? :) Biff, you are such a good teacher; you don't spoon-feed. Unfortunately, in this case, it's not so much about me not "thinking," it is a case of me not "remembering correctly" *and* am convinced by the examples that my theory is correct. You brought up a very good point. It doesn't make any difference if we use 1 or 100 when we use COUNT( ). But I think 100 can easily trick us (at least me) into thinking SUM ( ) even when COUNT( ) is staring at us. I have no problem with the Boolean though. This thread has helped me see COUNT, SUM and SUMPRODUCT as one big picture. I hope I can easily jump from one function to another in the future. I can't do it without the help from the group. Appreciated. Epinn "Biff" wrote in message ... Try this and see if you can figure out why it works. (just trying to "push" you to think) (don't use Evaluate Formula until you've given up) array entered =COUNT(IF((A1:A10="A")*(E1:E10="J"),100)) Biff "Epinn" wrote in message ... The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM, COUNT and SUMPRODUCT
Biff,
I figured. Have you ever been in the teaching profession? Good teachers are hard to find. I have discovered something interesting with LOOKUP( ) and have started a thread. On the subject of teaching "focus," have you read the following before? ************************************************** ****** Mary's father has five daughters: 1. Nana, 2. Nene, 3. Nini, 4. Nono. What is the name of the fifth daughter? Scroll down. Answer: Nunu? NO! Of course not. Her name is Mary. ************************************************** ****** Epinn "Biff" wrote in message ... It doesn't make any difference if we use 1 or 100 when we use COUNT( ). But I think 100 can easily trick us (at least me) into thinking SUM ( ) even when COUNT( ) is staring at us. That was my intention in using 100. Biff "Epinn" wrote in message ... Anyone wants to hear a joke? It's on me. I was stuck when I tried to come up with a formula using COUNT( ). Why? I thought I had to include an array in the formula for COUNT( ) to count and I couldn't figure out ...... I forgot that *values* are okay too. I tried to reference some COUNT( ) examples. Believe it or not, all of them have an array. There are scenarios that count without using an array but they use SUM( ) and the value "1." As a result, I am convinced that I need an array and I am more stuck. Today, I finally find an example of COUNT( ) counting value. Guess what, it is not purely counting value; it still counts an array *and* a value. Why are users more *inclined* to use SUM and 1 instead of COUNT and 1? Is there some kind of a "secret" that I am not aware of? :) Biff, you are such a good teacher; you don't spoon-feed. Unfortunately, in this case, it's not so much about me not "thinking," it is a case of me not "remembering correctly" *and* am convinced by the examples that my theory is correct. You brought up a very good point. It doesn't make any difference if we use 1 or 100 when we use COUNT( ). But I think 100 can easily trick us (at least me) into thinking SUM ( ) even when COUNT( ) is staring at us. I have no problem with the Boolean though. This thread has helped me see COUNT, SUM and SUMPRODUCT as one big picture. I hope I can easily jump from one function to another in the future. I can't do it without the help from the group. Appreciated. Epinn "Biff" wrote in message ... Try this and see if you can figure out why it works. (just trying to "push" you to think) (don't use Evaluate Formula until you've given up) array entered =COUNT(IF((A1:A10="A")*(E1:E10="J"),100)) Biff "Epinn" wrote in message ... The following formulae yield the same results. =SUMPRODUCT((A1:A10="a")*(E1:E10="j")) =SUM((A1:A10="a")*(E1:E10="J")) array formula =SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula =SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element. I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following. Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula: =COUNTIF(H1:H10,TRUE) Is it possible to use COUNT without a helper column? I am trying to learn here. Thank you. Epinn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT - Count Previous Month | Excel Worksheet Functions | |||
| sumproduct to count two arguments | Excel Worksheet Functions | |||
| sumproduct partial text count | Excel Worksheet Functions | |||
| How to count uniques of a SUMPRODUCT subset? | Excel Worksheet Functions | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
Linear Mode
