How can i make this formula simpler/shorter?

[movinghuman]

From 8 columns with 10 rows I need to know if data had been put in or not, code them as '1'; and then need the number of cells that have data put in. I managed to make it work this way, by using the formula below. But I wonder if I can do this more efficient/shorter....

=SUM(IF(AND(B4>0);"1";"0");(IF(AND(B5>0);"1";"0"));(IF(AND(B6>0);"1";"0"));(IF(AND(B7>0);"1";"0"));(IF(AND(B8>0);"1";"0"));(IF(AND(B9>0);"1";"0"));(IF(AND(B10>0);"1";"0"));(IF(AND(B11>0);"1";"0"));(IF(AND(B12>0);"1";"0"));(IF(AND(B13>0);"1";"0")); (IF(AND(C4>0);"1";"0"));(IF(AND(C5>0);"1";"0"));(IF(AND(C6>0);"1";"0"));(IF(AND(C7>0);"1";"0"));(IF(AND(C8>0);"1";"0"));(IF(AND(C9>0);"1";"0"));(IF(AND(C10>0);"1";"0"));(IF(AND(C11>0);"1";"0"));(IF(AND(C12>0);"1";"0"));(IF(AND(C13>0);"1";"0"));(IF(AND(D4>0);"1";"0"));(IF(AND(D5>0);"1";"0"));(IF(AND(D6>0);"1";"0"));(IF(AND(D7>0);"1";"0"));(IF(AND(D8>0);"1";"0"));(IF(AND(D9>0);"1";"0"));(IF(AND(D10>0);"1";"0"));(IF(AND(D11>0);"1";"0"));(IF(AND(D12>0);"1";"0"));(IF(AND(D13>0);"1";"0"));(IF(AND(E4>0);"1";"0"));(IF(AND(E5>0);"1";"0"));(IF(AND(E6>0);"1";"0"));(IF(AND(E7>0);"1";"0"));(IF(AND(E8>0);"1";"0"));(IF(AND(E9>0);"1";"0"));(IF(AND(E10>0);"1";"0"));(IF(AND(E11>0);"1";"0"));(IF(AND(E12>0);"1";"0"));(IF(AND(E13>0);"1";"0"));(IF(AND(F4>0);"1";"0"));(IF(AND(F5>0);"1";"0"));(IF(AND(F6>0);"1";"0"));(IF(AND(F7>0);"1";"0"));(IF(AND(F8>0);"1";"0"));(IF(AND(F9>0);"1";"0"));(IF(AND(F10>0);"1";"0"));(IF(AND(F11>0);"1";"0"));(IF(AND(F12>0);"1";"0"));(IF(AND(F13>0);"1";"0"));(IF(AND(G4>0);"1";"0"));(IF(AND(G5>0);"1";"0"));(IF(AND(G6>0);"1";"0"));(IF(AND(G7>0);"1";"0"));(IF(AND(G8>0);"1";"0"));(IF(AND(G9>0);"1";"0"));(IF(AND(G10>0);"1";"0"));(IF(AND(G11>0);"1";"0"));(IF(AND(G12>0);"1";"0"));(IF(AND(G13>0);"1";"0"));(IF(AND(H4>0);"1";"0"));(IF(AND(H5>0);"1";"0"));(IF(AND(H6>0);"1";"0"));(IF(AND(H7>0);"1";"0"));(IF(AND(H8>0);"1";"0"));(IF(AND(H9>0);"1";"0"));(IF(AND(H10>0);"1";"0"));(IF(AND(H11>0);"1";"0"));(IF(AND(H12>0);"1";"0"));(IF(AND(H13>0);"1";"0"));(IF(AND(I4>0);"1";"0"));(IF(AND(I5>0);"1";"0"));(IF(AND(I6>0);"1";"0"));(IF(AND(I7>0);"1";"0"));(IF(AND(I8>0);"1";"0"));(IF(AND(I9>0);"1";"0"));(IF(AND(I10>0);"1";"0"));(IF(AND(I11>0);"1";"0"));(IF(AND(I12>0);"1";"0"));(IF(AND(I13>0);"1";"0")))

Please, find included a test document in which I use this formula. Some data are included.

[Richard Buttrey]

Hi and welcome to the forum.

The answer is Yes. However please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

[kvsrinivasamurthy]

This works

Please Login or Register  to view this content.

[movinghuman]

Thanks, but how to deal with the fact that in the original file, the columns involved are not next to each other.... how do I solve that? or should I send another test file in which this is clear?
And could you explain a little (if possible) about the use of your solution? I am not that advanced in Excel

[kvsrinivasamurthy]

For the updated file

=SUMPRODUCT(1*($B$4:$I$13<>0))

[Fluff13]

Another option with that test file

Please Login or Register  to view this content.

[JohnTopley]

or

=COUNTIF($B4:$I13,"<>")

and please supply test file with actual format.

[movinghuman]

Thanks again for the responses; i created a new test file with the actual format for which the formula is used. Hopefully, this will work better.

[Richard Buttrey]

As John said, what's wrong with his COUNTIF() function?

Or indeed an =COUNTA(B4:AK13)

That said why not future proof your system and simplify your data entry with a normalised two dimensional table thereby allowing easy summary analysis and totalling

i.e.column labels for

City
Trainer Ref
Other Data Ref (if necessary, entries being Other Data A, Other Data B, ...etc)
Partner Number
Days

and add a new line for every record

[JohnTopley]

Try

=SUMPRODUCT(--(B$4:AG$13<>0)*(LEFT($B$2:$AG$2,6)="Expert"))

[kvsrinivasamurthy]

Formula

=SUMPRODUCT(($B$4:$AK$13>0)*($B$3:$AK$3="# days"))

[movinghuman]

Good morning all, thank you all for your contributions. As I am not an expert, I learn a lot from this forum!!!!

Your responses provide me with solutions to work with and include good advice for setting up the data file (the entire file, i.e. budget plannings tool for a Grant application, is rather multidimensional).

=SUMPRODUCT(--(B$4:AG$13<>0)*(LEFT($B$2:$AG$2,6)="Expert"))
=SUMPRODUCT(($B$4:$AK$13>0)*($B$3:$AK$3="# days"))

--> both these formulas worked as they skipped the columns in between with other data.

=COUNTIF($B4:$I13,"<>")
=COUNTIF($B$4:$AK$13;">=1")
--> these solutions did not work as they did not skip columns in between with other data.

So, for me to trial and error and learn, I tried this version: =COUNTIF(($B4:$B13;F4:F13;K4:K13;O4:O13;T4:T13;X4:X13;AC4:AC13;AG4:AG13);">0")
However, gave me back error; I assume I can only use 1 array definition in this formula? Or should I define the arrays differently?

[avk]

Please Login or Register  to view this content.