Hi,

I'm trying to count the number of 1s and 0s in certain rows.

e.g.

A 1 0 1
B 0
A 1
A
B 1 0
B 1 1



The 1s are outstanding observations and 0s are resolved observations. This obviously means by counting the number of 1s we know how many observations are outstanding.

However we are coming to an end of year audit and want to count how many observations were raised in total for each company(where A and B are company names and each row represents a location in that company).

I'm trying to get a 'concise' formula which will add all the 1s and 0s in a row depending on if it is company A or B. I've tried playing around with arrays and haven't had much luck.

Any help appreciated!

Cheers,

Dan

Hi smithy4564

Hope I am on the right track!
Assuming the data you posted is in cells A1:D6.
=SUMPRODUCT((A1:A6="A")*(B1:D60)) change "A" to "B" or put them in cell that you can reference to. (May be Data Validation).

Unfortunately that's a bit too simple.

What the message didn't show is that there are large gaps in between data. There's around 100 columns, of which only a few will have a 1 or a 0. I also need to count the 1s and the 0s.

Also I've found using SUMPRODUCT/COUNTIF etc that they work for small simple amounts of data, so I could do that for, say, each column and add them but I'm looking for a more eloquent solution.

I've also had problems differentiating between a 'gap' and a 0.

I'm guessing I need an array but, as with the SUMPRODUCT/COUNTIF etc I can't get it to work for a large table where it conditionally counts what is on certain rows.

Perhaps you need to upload a sample file with expected result.

Try:
=SUMPRODUCT((A1:A2000="B")*(B1:I2000<""))
Change columns to suit your data etc.

Kevin, I've tried something similar multiple times and it didn't work....

...your example however works perfectly!

Thanks very much for the help.

Regards,

Dan

No problem Dan, glad your sorted.