SUMIF Question
 

Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any cell in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel

SUMIF Question
 

Hi Nigel

Try...
=SUMIF(A1:A5,"<"&"",B1:B5)
--

XL2003
Regards

William



"Nigel" wrote in message
...
Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any cell in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT
blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel

SUMIF Question
 

=SUMPRODUCT(--NOT(ISBLANK(A1:A5)),B1:B5)
will do the trick

Or you could take your original formula and deduct that from SUM(B1:B5)

--
Greetings from New Zealand
Bill K

"Nigel" wrote in message
...
Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any cell in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT
blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel

SUMIF Question
 

That does the trick - many thanks

--
Cheers
Nigel



"William" wrote in message
...
Hi Nigel

Try...
=SUMIF(A1:A5,"<"&"",B1:B5)
--

XL2003
Regards

William



"Nigel" wrote in message
...
Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any cell
in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT
blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel

SUMIF Question
 

Hi Bill
Thanks for the tip - nice elegant solution

Greetings from the UK!!

--
Cheers
Nigel



"Bill Kuunders" wrote in message
...

=SUMPRODUCT(--NOT(ISBLANK(A1:A5)),B1:B5)
will do the trick

Or you could take your original formula and deduct that from SUM(B1:B5)

--
Greetings from New Zealand
Bill K

"Nigel" wrote in message
...
Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any cell
in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT
blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel

SUMIF Question
 

Just to add:

=SUMIF(A1:A5,"<",B1:B5)

Should be sufficient

--
Regards,
Tom Ogilvy


"Nigel" wrote in message
...
That does the trick - many thanks

--
Cheers
Nigel



"William" wrote in message
...
Hi Nigel

Try...
=SUMIF(A1:A5,"<"&"",B1:B5)
--

XL2003
Regards

William



"Nigel" wrote in message
...
Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any cell
in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT
blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel

SUMIF Question
 

Of course it is
Thanks Tom
Learning each day.
Regards
Bill K

"Tom Ogilvy" wrote in message
...
Just to add:

=SUMIF(A1:A5,"<",B1:B5)

Should be sufficient

--
Regards,
Tom Ogilvy


"Nigel" wrote in message
...
That does the trick - many thanks

--
Cheers
Nigel



"William" wrote in message
...
Hi Nigel

Try...
=SUMIF(A1:A5,"<"&"",B1:B5)
--

XL2003
Regards

William



"Nigel" wrote in message
...
Hi All
I use the SUMIF worksheet function to SUM a range conditionally, eg

SUMIF(A1:A5,"",B1:B5), which works great for summing B1:B5 if any
cell
in
A1:A5 is blank

I now wish to reverse the logic and sum if any cell in A1:A5 are NOT
blank,
but

SUMIF(A:A5,NOT(""),B1:B5) does not work !

Please help

--
Cheers
Nigel