COUNTIF Array

[Pyrite]

Hi,

I have tried to figure this one out for myself using previous advice offered
but it has me baffled!!

I have 5 columns, Service, Sales, Maintenance, Fault, Non Fault. This
records accidents so if driver has a tick in the column then either Fault or
Non Fault will also contain a tick. I want to count which accidents are our
fault but because there are multiple driver columns I cant just do a COUNTIF
on the fault column, i need it for each driver type.

Lets simply use Service drivers for an example. I need to COUNTIF Service =
Yes AND Fault = Yes.

Service Fault Non Fault
x x
x x
x x
x x
x x

The Count here would need to equal 3 as there are 5 service accidents but
only 3 which are our fault.

I will then use this formula 3 times to provide a count for which of the
service acccidents were our fault, which of the sales accidents were our
fault and which of the maintenance accidents were our fault.

Thanks in advance.

 

[Sheeloo]

You need to understand SUMPRODUCT and use it.
Start with learning ARRAY formula.

One of us can give you the formula if you do not want to learn... but it
will help you more in the long run

 

[Pyrite]

Thanks Sheeloo,

I am very interested in learning, I tried to learn from the last array
formula I was given but found it very difficult due to limited help on the
Excel pages. If you know of any resources better at explaining array formula
I would very much appreciate that information.

I read through the SUMPRODUCT help but didn't think that it fit my use as it
adds the values of the cells whereas I want the toal number of cells
containing any value (in this case "ü" which gives a tick in Wingdings). I
will re read it though and see if I have missed something.

 

[Sheeloo]

An excellent source is http://www.cpearson.com/excel/ArrayFormulas.aspx

If you follow the example (=SUM((A2:A10="Fax")*(B2:B10="Brown")*(C2:C10)))
there it will be very easy for you to find your solution.

I will work on your problem and give you a solution in a couple of hours if
someone else has not done it or you have not figured it out yourself.

 

[Sheeloo]

Assuming your data is in A1:E17

Put this in A18 (hold CTRL and SHIFT keys while pressing ENTER otherwise
this will not work. This is the way ARRAY formulae are entered.

=SUMPRODUCT(--(A2:A17<>""),--($D$2:$D$17="X"))
[This will give you the count of rows where there is a non blank cell in
column A and corresponding cell in column D has an X]

Copy to B18 and C18 to get similar count for B & C

You can replace A2:A17 by A:A if you put the formula anywhere other than
columns A-E.

Let me know how it works.

 

[Pyrite]

I am not sure where you are posting from but I'm guessing America. I don't
want you to think I cheated so to explain the last post I saw before I left
work last night was the second post that I made. I came in this morning (now)
and saw two return posts from you. I have at this point only read the first
one of these and have worked out my own solution using your example. The only
thing I don't really understand is the use of SUM instead of COUNTIF but I'm
sure this will become clear when I get time to read the document you have
recommended. Thankyou for guiding me to find my own answer, I would genuinely
much rather do it like this.

To see if I have got there correctly and before I look at your second post
(which I'm guessing contains the answer to my problem) the formula that I
have created is {=SUM((E139:E149="ü")*(H139:H149="ü"))}

The only bit that was really throwing me off in the first place was the SUM
part, I had the array bits right in all the things I tried but COUNTIF was
throwing it off.

Thanks again.

 

[Pyrite]

Hmmm, your final formula is different to mine yet they both work. The beauty
of Excel I suppose, that there are multiple ways to get a single result. If I
am right yours looks for cells in the 'driver' column with anything at all in
them and then looks to the 'fault' column for a specified symbol (in this
case x) and counts those cells. The way I have done it the forumla looks down
both columns and counts how many times corresponding cells have a set
character in them. I think....

I'm not sure which will work better, I'm inclined to think yours is more
future proof as any symbol can be used in the driver column.

Thanks again for all your help and guidance Sheeloo, I'm starting to
understand array formula a little better.

Assuming your data is in A1:E17

Put this in A18 (hold CTRL and SHIFT keys while pressing ENTER otherwise
this will not work. This is the way ARRAY formulae are entered.

=SUMPRODUCT(--(A2:A17<>""),--($D$2:$D$17="X"))
[This will give you the count of rows where there is a non blank cell in
column A and corresponding cell in column D has an X]

Copy to B18 and C18 to get similar count for B & C

You can replace A2:A17 by A:A if you put the formula anywhere other than
columns A-E.

Let me know how it works.

Thanks Sheeloo,

I am very interested in learning, I tried to learn from the last array
formula I was given but found it very difficult due to limited help on the
Excel pages. If you know of any resources better at explaining array formula
I would very much appreciate that information.

I read through the SUMPRODUCT help but didn't think that it fit my use as it
adds the values of the cells whereas I want the toal number of cells
containing any value (in this case "ü" which gives a tick in Wingdings). I
will re read it though and see if I have missed something.

 

[Sheeloo]

Yes, you are rigt. I am on west coast of US.

Both formulae are essentially same. They take arrays of values, mark as TRUE
where condition is met, convert them to 1 or 0 and then add them up.
Truth Table for AND
True and True = True
All other combination False

Glad you took the time to learn. Self help is the best help.

Hmmm, your final formula is different to mine yet they both work. The beauty
of Excel I suppose, that there are multiple ways to get a single result. If I
am right yours looks for cells in the 'driver' column with anything at all in
them and then looks to the 'fault' column for a specified symbol (in this
case x) and counts those cells. The way I have done it the forumla looks down
both columns and counts how many times corresponding cells have a set
character in them. I think....

I'm not sure which will work better, I'm inclined to think yours is more
future proof as any symbol can be used in the driver column.

Thanks again for all your help and guidance Sheeloo, I'm starting to
understand array formula a little better.

Assuming your data is in A1:E17

Put this in A18 (hold CTRL and SHIFT keys while pressing ENTER otherwise
this will not work. This is the way ARRAY formulae are entered.

=SUMPRODUCT(--(A2:A17<>""),--($D$2:$D$17="X"))
[This will give you the count of rows where there is a non blank cell in
column A and corresponding cell in column D has an X]

Copy to B18 and C18 to get similar count for B & C

You can replace A2:A17 by A:A if you put the formula anywhere other than
columns A-E.

Let me know how it works.

Thanks Sheeloo,

I am very interested in learning, I tried to learn from the last array
formula I was given but found it very difficult due to limited help on the
Excel pages. If you know of any resources better at explaining array formula
I would very much appreciate that information.

I read through the SUMPRODUCT help but didn't think that it fit my use as it
adds the values of the cells whereas I want the toal number of cells
containing any value (in this case "ü" which gives a tick in Wingdings). I
will re read it though and see if I have missed something.

:

You need to understand SUMPRODUCT and use it.
Start with learning ARRAY formula.

One of us can give you the formula if you do not want to learn... but it
will help you more in the long run

:

Hi,

I have tried to figure this one out for myself using previous advice offered
but it has me baffled!!

I have 5 columns, Service, Sales, Maintenance, Fault, Non Fault. This
records accidents so if driver has a tick in the column then either Fault or
Non Fault will also contain a tick. I want to count which accidents are our
fault but because there are multiple driver columns I cant just do a COUNTIF
on the fault column, i need it for each driver type.

Lets simply use Service drivers for an example. I need to COUNTIF Service =
Yes AND Fault = Yes.

Service Fault Non Fault
x x
x x
x x
x x
x x

The Count here would need to equal 3 as there are 5 service accidents but
only 3 which are our fault.

I will then use this formula 3 times to provide a count for which of the
service acccidents were our fault, which of the sales accidents were our
fault and which of the maintenance accidents were our fault.

Thanks in advance.