Here's another one I've been mucking around with and can't get. In cell C7 I
want a formula that will look at the date in A7 and then go to A30:A1000 find
those dates and then how many of them have the answer "Yes" in D30:D1000.
This one is a little more complicated. Thank you so much. Connie

One way:

=SUMPRODUCT(--(A30:A1000=A7),--(D30:D1000="Yes"))

HTH,
Paul

--

"Connie Martin" wrote in message
...
Here's another one I've been mucking around with and can't get. In cell
C7 I
want a formula that will look at the date in A7 and then go to A30:A1000
find
those dates and then how many of them have the answer "Yes" in D30:D1000.
This one is a little more complicated. Thank you so much. Connie

I don't understand parts of that formula but it works, and right now when I'm
pressed for time, that's all that matters, and I thank you very, very much!!

Connie

"PCLIVE" wrote:

One way:

=SUMPRODUCT(--(A30:A1000=A7),--(D30:D1000="Yes"))

HTH,
Paul

--

"Connie Martin" wrote in message
...
Here's another one I've been mucking around with and can't get. In cell
C7 I
want a formula that will look at the date in A7 and then go to A30:A1000
find
those dates and then how many of them have the answer "Yes" in D30:D1000.
This one is a little more complicated. Thank you so much. Connie

There is another way to write the formula:
=SUMPRODUCT((A30:A1000=A7)*(D30:D1000="Yes"))

However, I prefer the first suggested way.

The double minus (--) converts each item matching the criteria to one (1)
and non-matches to zero (0). The SUMPRODUCT function multiplies each of the
corresponding rows together which will result to either a 1 (if both are a
match) or a zero if they don't both match. So you say, what if one matches
and the other doesn't. Well the match equals a 1 and the non-match equals a
0. 1 times 0 = 0. In the end, you get a count of all the rows that match
the criteria that you specified.

Glad it's working.

The SUMPRODUCT function is commonly used when you want to get a count of how
many rows match more than one criteria. However, if you wanted to get the
SUM of all the rows where some certain columns match your specified
criteria, then you could add this to the formula.

=SUMPRODUCT(--(A30:A1000=A7),--(D30:D1000="Yes"),E30:E1000)

Column E being the column that you want to SUM.

Regards,
Paul




--

"Connie Martin" wrote in message
...
I don't understand parts of that formula but it works, and right now when
I'm
pressed for time, that's all that matters, and I thank you very, very
much!!

Connie

"PCLIVE" wrote:

One way:

=SUMPRODUCT(--(A30:A1000=A7),--(D30:D1000="Yes"))

HTH,
Paul

--

"Connie Martin" wrote in message
...
Here's another one I've been mucking around with and can't get. In
cell
C7 I
want a formula that will look at the date in A7 and then go to
A30:A1000
find
those dates and then how many of them have the answer "Yes" in
D30:D1000.
This one is a little more complicated. Thank you so much. Connie