SumProduct Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi Gary
just use the previous SUMPRODUCT formula with an added parameter:
=sumproduct((A1:Z1="O")*(A2:Z2="D"),A3:Z3)

HTH
 
One way

=SUMPRODUCT((A2:M2&A3:M3="OD")*A4:M4)

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Gary said:
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
Click to expand...
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
Click to expand...
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
 
Back
Top