Count a Match set of values between cells

[John M.]

I want to produce a page within my workbook for results from my work page.
On my work page and have two columns F and G which down the page will have an
array of entries in each. I want to count the number of occurances where
something in column F and something in column G are equal to two different
values. This is my example.
F G
TYPE GLAZING
5 S SG-3
6 C
7 S SG-3
8 C
9 S SG-3

For my results I want to know how many times that "S" and "SG-3" occur side
by side in the same row as shown above. Can someone please enlighten me how
to accomplish this task?

Thank you,
John M.

 

[Lars-Åke Aspelin]

I want to produce a page within my workbook for results from my work page.
On my work page and have two columns F and G which down the page will have an
array of entries in each. I want to count the number of occurances where
something in column F and something in column G are equal to two different
values. This is my example.
F G
TYPE GLAZING
5 S SG-3
6 C
7 S SG-3
8 C
9 S SG-3

For my results I want to know how many times that "S" and "SG-3" occur side
by side in the same row as shown above. Can someone please enlighten me how
to accomplish this task?

Thank you,
John M.

Try this formula:

=SUMPRODUCT((F5:F9="S")*(G5:G9="SG-3"))

Change the 5 and 9 on both places to cover the rows of data that you
have.

Hope this helps / Lars-Åke

 

[PCLIVE]

Try this:

=SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3"))

If you want the "S" and "SG-3" to referenced by a particular cell:
=SUMPRODUCT(--(F2:F100=H1),--(G2:G100=H2))

Regards,
Paul

 

[John M.]

Thanks guys this function did the trick but it is missing one thing and that
is the ability to overlook blank cells and return a false result. How would
I properly add that into the formula?

 

[PCLIVE]

If the cells are blank, then it should not be counted as a match. If there
are no matches, then the result should be zero. Do you mean that if there
are no matches to both criteria, then you want the result to be false? If
so then:

=IF(SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3"))=0,"False",SUMPRODUCT(--(F2:F100="S"),--(G2:G100="SG-3"))

If you meant something else, please elaborate.

Regards,
Paul


--