Help with Count, CountIf, Sum Functions

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