Array Formula??

[Ally]

Hi

I have a column in Excel where I would like to sum up those with the values of "No Scan Date" and "No Arrival Date".

The column would look like this (but a lot longer)!:

[B]Time from Arrival to Scan (minutes)[/B]
170
266
No Scan Date
15
1213
No Scan Date
945
1135
32
55
No Arrival Date
731
No Scan Date

I've tried a variety of versions of array formulas but am getting nowhere a couple of which are:

{=SUM(IF('Mar 10'!AD5:AD65536="No Scan Date",IF('Mar 10'!AD5:AD65536="No Arrival Date",1,0),0))}
and 
{=SUM(('Mar 10'!AD5:AD72="No Scan Date")*('Mar 10'!AD5:AD72="No Arrival Date"))}

These both give me a result of 0 when it should actually be 4 (3 "No scan date" and 1 "No Arrival Date")

Is anyone able to tell me where I'm going wrong please?!

Thanks - Ally

 

[Ally]

Thank you but I've been on OzGrid all morning - been using a few of their examples but not understanding why it's not working.

I've kind of found a way around it - non-array style -

=COUNTIF('Mar 10'!AD5:AD65536,"No Scan Date")+COUNTIF('Mar 10'!AD5:AD65536,"No Arrival Date")

But am not sure if this is the most efficient way of doing it!?

Tnx
Ally

 

[MI man]

Have to tried SUMPRODUCT..??

 

[Ally]

I was just thinking about trying that when you replied. I'll do it now.

Perhaps the array doesn't work because it's in the one column???

 

[Ally]

Apparently, SUMPRODUCT treats non-numeric entries as zero, where the entries I need it to count are text. Also I couldn't see any way of entering criteria.

Back to square 1.

 

[MI man]

=SUMPRODUCT((A2:A65536="No scan date")+(A2:A65536= "Arrival date"))

This would solve the issue.

 

[Ally]

Fantastic - thank you!

I wonder why the help says that SUMPRODUCT treats text values as zero though?!

Thanks for your help!

Ally

 

[Brianwarnock]

Why should we assume that sunproduct is quicker than the 2 countif statement?

This
{=SUM(IF('Mar 10'!AD5:AD65536="No Scan Date",IF('Mar 10'!AD5:AD65536="No Arrival Date",1,0),0))}
I think should be
{=SUM(IF('Mar 10'!AD5:AD65536="No Scan Date",1,IF('Mar 10'!AD5:AD65536="No Arrival Date",1,0))}

I wonder why the help says that SUMPRODUCT treats text values as zero though?!

Well it does in the normal use of multiplying arrays but in the example by MI he is adding the results of 2 conditional statements, ie summing the number of TRUE conditions, a true condition returns a 1 for SUMPRODUCT.


Brian

 

[Ally]

Thanks so much Brian. That was exactly what I was after but just couldn't manage it!

Thanks for you help - both of you!

 

[CHSBK]

You might be interested in this:

=COUNTIF('Mar 10'!AD5:AD65536,"No*")

Since each of the cells you want to count start swith the word 'No', the above will also return a result of 4. The asterisk is a wildcard, allowing any combination of characters after the 'No'.

Just thought you may like this approach as the formula is shorter in length.

 

[Ally]

You might be interested in this:

=COUNTIF('Mar 10'!AD5:AD65536,"No*")

Since each of the cells you want to count start swith the word 'No', the above will also return a result of 4. The asterisk is a wildcard, allowing any combination of characters after the 'No'.

Just thought you may like this approach as the formula is shorter in length.

Thank you! Why did I not think of that before!!!? That's great.