I am having the worst time trying to wrap my head around this function.
I just can't seem to visualize how to do this. I have a spreadsheet
with the following:

A B C D
1 6 9
2 6 8 8
3 7 3
4 3 5 8


I need to know how many are in A but don't have anything in B. How many
are in C but don't have anything in A or D. Things of this nature but I
just can't seem to find a way of doing it. I am currently using the
functions in this order SUM, COUNTIF, IF but it is returning incorrect
results. Can anyone out there with a better math mind help me to figure
this out.


Thanks so much

Kris

=sumproduct(--(A1:A10=""),--(C1:C10=""),B1:B10)

as an example.

--
Regards,
Tom Ogilvy


"Kris" wrote:

I am having the worst time trying to wrap my head around this function.
I just can't seem to visualize how to do this. I have a spreadsheet
with the following:

A B C D
1 6 9
2 6 8 8
3 7 3
4 3 5 8


I need to know how many are in A but don't have anything in B. How many
are in C but don't have anything in A or D. Things of this nature but I
just can't seem to find a way of doing it. I am currently using the
functions in this order SUM, COUNTIF, IF but it is returning incorrect
results. Can anyone out there with a better math mind help me to figure
this out.


Thanks so much

Kris

Hi Kris,

I need to know how many are in A but don't have anything in B:
=SUMPRODUCT(--ISNUMBER(A1:A4),--ISBLANK(B1:B4))

How many are in C but don't have anything in A or D:
=SUMPRODUCT(--ISNUMBER(C1:C4),--ISBLANK(A1:A4),--ISBLANK(D1:D4))

Regards,
Ivan

This is great guys thanks for the help. I have been having trouble
modifying this to work in all my situations though, and I think I have
figured out why. I have Text in my main column and dates in the other
two and I don't think SUMPRODUCT is going to let me count it like that.
Perhaps I am wrong. Can you all help again?


Thank you

I thought I pretty much knew everything about the standard functions in
Excel, but this thread has something I've never encountered. What does
"--" before the function do?

Ivan Raiminius wrote:
Hi Kris,

I need to know how many are in A but don't have anything in B:
=SUMPRODUCT(--ISNUMBER(A1:A4),--ISBLANK(B1:B4))

How many are in C but don't have anything in A or D:
=SUMPRODUCT(--ISNUMBER(C1:C4),--ISBLANK(A1:A4),--ISBLANK(D1:D4))

Regards,
Ivan