SumProduct Function  
Author Message
MJEgner





PostPosted: 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





PostPosted: 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





PostPosted: 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





PostPosted: Mon Feb 02 15:46:08 CST 2004 Top

worksheet functions >> SumProduct Function Thanks again Frank. Appreciate all the help!