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.

"Pyrite" wrote:

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.

"Sheeloo" wrote:

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" wrote:

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" wrote:

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" wrote:

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.