|
|
|
Author |
Message |
MJEgner
|
Posted: Mon Feb 02 13:17:08 CST 2004 |
Top |
worksheet functions >> SumProduct Function
Anybody have any idea how to solve a double countif problem.
eg. Row 1 ---> O B A C O D O
Row 2 ---> D A W M S D D A A
Row 3 ---> 1 2 4 3 2 1 4 3 1
I need to do a count based on the conditions of having an "O" in row#1 and also that "O" observation being an "D" in row#2. In the simpliest case, =countif(row1,"=O") would do the job, but I don't know how to solve the problem when Row2 is introduced. Only idea is to insert another row with a formula serving as a filter.
Anybody have any ideas using formuls or VB code to solve the problem. Many thanks to all!
----
Two earlier people were able to provide the very helpful suggestion (that worked) of using:
=sumproduct((A1:Z1="O")*(A2:Z2="D")
One further wrinkle, what if Row #3 was introduced and I wanted to sum up those value. Case in example, I wanted to find the sum of all instance of "O" in row1 & "D" in row2 and in such a case, add up the total values in row#3 the joint case
eg. Row 1 ---> O B A C O D O
Row 2 ---> D A W M S D D A A
Row 3 ---> 1 2 4 3 2 1 4 3 1
Thanks.
Excel457
|
|
|
|
|
Frank
|
Posted: Mon Feb 02 13:17:08 CST 2004 |
Top |
worksheet functions >> SumProduct Function
Hi Gary
just use the previous SUMPRODUCT formula with an added parameter:
=sumproduct((A1:Z1="O")*(A2:Z2="D"),A3:Z3)
HTH
Frank
> Anybody have any idea how to solve a double countif problem.
>
> eg. Row 1 ---> O B A C O D O
> Row 2 ---> D A W M S D D A A
> Row 3 ---> 1 2 4 3 2 1 4 3 1
>
> I need to do a count based on the conditions of having an "O" in
> row#1 and also that "O" observation being an "D" in row#2. In the
> simpliest case, =countif(row1,"=O") would do the job, but I don't
> know how to solve the problem when Row2 is introduced. Only idea is
> to insert another row with a formula serving as a filter.
>
> Anybody have any ideas using formuls or VB code to solve the problem.
> Many thanks to all!
>
> ----
> Two earlier people were able to provide the very helpful suggestion
> (that worked) of using:
>
> =sumproduct((A1:Z1="O")*(A2:Z2="D")
>
> One further wrinkle, what if Row #3 was introduced and I wanted to
> sum up those value. Case in example, I wanted to find the sum of all
> instance of "O" in row1 & "D" in row2 and in such a case, add up the
> total values in row#3 the joint case
>
> eg. Row 1 ---> O B A C O D O
> Row 2 ---> D A W M S D D A A
> Row 3 ---> 1 2 4 3 2 1 4 3 1
>
> Thanks.
|
|
|
|
|
Leo
|
Posted: Mon Feb 02 13:39:48 CST 2004 |
Top |
worksheet functions >> SumProduct Function
One way
=SUMPRODUCT((A2:M2&A3:M3="OD")*A4:M4)
--
Best Regards
Leo Heuser
Followup to newsgroup only please.
> Anybody have any idea how to solve a double countif problem.
>
> eg. Row 1 ---> O B A C O D O
> Row 2 ---> D A W M S D D A A
> Row 3 ---> 1 2 4 3 2 1 4 3 1
>
> I need to do a count based on the conditions of having an "O" in row#1 and
also that "O" observation being an "D" in row#2. In the simpliest case,
=countif(row1,"=O") would do the job, but I don't know how to solve the
problem when Row2 is introduced. Only idea is to insert another row with a
formula serving as a filter.
>
> Anybody have any ideas using formuls or VB code to solve the problem.
Many thanks to all!
>
> ----
> Two earlier people were able to provide the very helpful suggestion (that
worked) of using:
>
> =sumproduct((A1:Z1="O")*(A2:Z2="D")
>
> One further wrinkle, what if Row #3 was introduced and I wanted to sum up
those value. Case in example, I wanted to find the sum of all instance of
"O" in row1 & "D" in row2 and in such a case, add up the total values in
row#3 the joint case
>
> eg. Row 1 ---> O B A C O D O
> Row 2 ---> D A W M S D D A A
> Row 3 ---> 1 2 4 3 2 1 4 3 1
>
> Thanks.
|
|
|
|
|
anonymous
|
Posted: Mon Feb 02 15:46:08 CST 2004 |
Top |
worksheet functions >> SumProduct Function
Thanks again Frank. Appreciate all the help!
|
|
|
|
|
|
|