conditional sum

S

smithy4564

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 man
observations are outstanding.

However we are coming to an end of year audit and want to count how man
observations were raised in total for each company(where A and B ar
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 0
in a row depending on if it is company A or B. I've tried playing aroun
with arrays and haven't had much luck.

Any help appreciated!

Cheers,

Da
 
K

Kevin@Radstock

Hi smithy4564

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

Kevin@Radstock

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

Try:
=SUMPRODUCT((A1:A2000="B")*(B1:I2000<>""))
 
S

smithy4564

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

...your example however works perfectly!

Thanks very much for the help.

Regards,

Da
 
K

Kevin@Radstock

No problem Dan, glad your sorted.

smithy4564;1609123 said:
Kevin, I've tried something similar multiple times and it didn'
work....

...your example however works perfectly!

Thanks very much for the help.

Regards,

Da
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top