Hi,
ok i have 2 columns....



Column A Column B

32 -

54 20

45 -

65 76

75 75



I need to add the number of times Column A and Column B both have values.
SO in this case its 3 times.

I'm not sure if i use count if or sumproduct and how. I tried:
=SUMPRODUCT(--(G5:G15<>""),--(H5:H15<>"")) but its giving the wrong value.
value.

>=SUMPRODUCT(--(G5:G15<>""),--(H5:H15<>""))

That should work.

See if this works:

=SUMPRODUCT(--(ISNUMBER(G5:G15)),--(ISNUMBER(H5:H15)))

--
Biff
Microsoft Excel MVP


"excelllllllll" <(E-Mail Removed)> wrote in message
news95104B6-3A61-4E2C-9A82-(E-Mail Removed)...
> Hi,
> ok i have 2 columns....
>
>
>
> Column A Column B
>
> 32 -
>
> 54 20
>
> 45 -
>
> 65 76
>
> 75 75
>
>
>
> I need to add the number of times Column A and Column B both have values.
> SO in this case its 3 times.
>
> I'm not sure if i use count if or sumproduct and how. I tried:
> =SUMPRODUCT(--(G5:G15<>""),--(H5:H15<>"")) but its giving the wrong value.
> value.
>
>
>
>

You could try using ISNUMBER, eg:
=SUMPRODUCT((ISNUMBER(A5:A15))*(ISNUMBER(B5:B15)))
Success? hit the YES below
--
Max
Singapore
---
"excelllllllll" wrote:
> Column A Column B
>
> 32 -
>
> 54 20
>
> 45 -
>
> 65 76
>
> 75 75
>
>
>
> I need to add the number of times Column A and Column B both have values.
> SO in this case its 3 times.
>
> I'm not sure if i use count if or sumproduct and how. I tried:
> =SUMPRODUCT(--(G5:G15<>""),--(H5:H15<>"")) but its giving the wrong value.
> value.
>
>
>

Hey I tried that and it still doesnt work. it counts all th numbers in
column A only.

I need so it only counts when numbers appear in both columns only.

"Max" wrote:

> You could try using ISNUMBER, eg:
> =SUMPRODUCT((ISNUMBER(A5:A15))*(ISNUMBER(B5:B15)))
> Success? hit the YES below
> --
> Max
> Singapore
> ---
> "excelllllllll" wrote:
> > Column A Column B
> >
> > 32 -
> >
> > 54 20
> >
> > 45 -
> >
> > 65 76
> >
> > 75 75
> >
> >
> >
> > I need to add the number of times Column A and Column B both have values.
> > SO in this case its 3 times.
> >
> > I'm not sure if i use count if or sumproduct and how. I tried:
> > =SUMPRODUCT(--(G5:G15<>""),--(H5:H15<>"")) but its giving the wrong value.
> > value.
> >
> >
> >

Nevermind my previous post!
it totally worked!!!

thanks!

"Max" wrote:

> You could try using ISNUMBER, eg:
> =SUMPRODUCT((ISNUMBER(A5:A15))*(ISNUMBER(B5:B15)))
> Success? hit the YES below
> --
> Max
> Singapore
> ---
> "excelllllllll" wrote:
> > Column A Column B
> >
> > 32 -
> >
> > 54 20
> >
> > 45 -
> >
> > 65 76
> >
> > 75 75
> >
> >
> >
> > I need to add the number of times Column A and Column B both have values.
> > SO in this case its 3 times.
> >
> > I'm not sure if i use count if or sumproduct and how. I tried:
> > =SUMPRODUCT(--(G5:G15<>""),--(H5:H15<>"")) but its giving the wrong value.
> > value.
> >
> >
> >