| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF Question | Excel Discussion (Misc queries) | |||
| SUMIF question | Excel Discussion (Misc queries) | |||
| SUMIF Question | Excel Programming | |||
| sumif question | Excel Programming | |||
| sumif question 2 | Excel Programming | |||
Linear Mode
