Combining discontinuous cell with AND function with the same criteria

[Sanga Fanai]

A B
1 No. of passed pupils 5
.
.
20 No. of passed pupils 10
.
.
30 No. of passed pupils 20

I have formula on (say) D1 [=if(AND(b1="",b20="",b30=""),"",sum(b1,b20,b30))]

I try to combine the "AND" part which have the same criteria (i.e. blank) like this =if(AND(b1,b20,b30)="","",sum(b1,b20,b30)) and it does not work.

Any help....or it is not possible at all using "AND" function

[AliGW]

You could try this:

=B1&B20&B30=""

[Sam Capricci]

if the first works why change it? but this could work =IF(sum(B1,B20,B30)=0,"",Sum(b1,b20,b30))

[Sanga Fanai]

Thanks Ali...it works perfectly

And Sambo, i need the outcome 0 sometimes. If there are no pupils passed, i need to display 0. I want to put blank only if b1, b20 and b30 are blank
Thanks anyway

[AliGW]

Glad to have helped!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Sanga Fanai]

if the first works why change it? but this could work =IF(sum(B1,B20,B30)=0,"",Sum(b1,b20,b30))

Sambo, my original file is big, not only those three cells (b1,b20 and b30) and sometimes i need to look for same criteria other than blank, and i don't want to type the same criteria again and again...
I could use your suggestion in some of my project. Thanks