Problem with 2 conditions in order to count

**koncurn** · 03-30-2010, 12:05 PM

Here is my problem. I need to look at D2:D607 and see if equals DTW.If it does, then I only want to count it if cells H2:H607 are nonblank. I've tried a few ways but they have all had errors with the structure. Please help me get on the right track. Thank you so much in advance for any help you can provide me.

**Paul** · 03-30-2010, 12:10 PM

Hi Koncurn, welcome to the forum.

Try this formula:

=SUMPRODUCT(--(D2:D607="DTW"),--(H2:H607<>""))

**NBVC** · 03-30-2010, 12:10 PM

Try:

=Sumproduct(--(D2:D607="DTW"),--(H2:H607<>""))

**koncurn** · 03-30-2010, 12:23 PM

Thank you so much as this worked like a charm. Could you explain what the--in the equation means so that I can learn and help myself more? Thanks again.

**Paul** · 03-30-2010, 12:30 PM

The two dashes are called unary operators (double unary in this case). It serves to coerce (convert) the TRUE/FALSE values returned by each condition of the SUMPRODUCT into 1's and 0's in order to perform mathematical calculations on them.

For example, in your formula you'd get something like:

=SUMPRODUCT(--(D2:D607="DTW"),--(H2:H607<>""))
=SUMPRODUCT(--({TRUE;TRUE;FALSE;FALSE...}),--({TRUE;FALSE;FALSE;TRUE;...}))
=SUMPRODUCT({1;1;0;0;...},{1;0;0;1;...})
=18

Hope that makes sense on a basic level. There's many threads discussing/arguing SUMPRODUCT and using double-unary operators vs. multiplication, e.g. (D2:D607="DTW")*(H2:H607<>""), but you can usually use both interchangeably.