CountA occurence(s) in a table with array formula

P

Pierre PVZ

Hi

Here's an example of what I'm trying to to (the table I handle is around 5000 to 10000 records
Blabla1 BILL1 Atyp
Blabla2 BILL1 Btyp
Blabla3 BILL2 Atyp
Blabla4 BILL1 Atyp
Blabla5 BILL2 Btyp

I "just" need to summarize in a table on another sheet the number of occurences (records) like thi
A B
1 Atype Btyp
2 BILL1 occurX occur
3 BILL2 occurY occur

I use the following array formula in B2, for example, and I use named range in the data tabl
{=SUM(IF((name_type=B$1)*(name_BILL=$A2);1;0))

I really need to avoid pivottable for internal reasons. The summary table should be "usable" by anybody in my company, and none of them know how to use pivots. It is much easier to show the where the summary table is, and easier to handle, modify and format (condtions,...

My nightmare is I get a #NA...
I don't know why / I don't remember what could cause this #N

Can anyone give me hints on this case ? Thks in advanc

Pierre PVZ
 
P

Pete McCosh

Pierre,

a couple of things.

Firstly, this is probably just a local setting, but the
parts of the If are normally separated by , rather than ;

You could actually simplify things and do away with the IF
entirely:

{=sum((name_type=B$1)*(name_BILL=$A2))}

Second, are the entries you're checking calculated or not?
A quick experiment has shown that an error in any of the
cells you're checking will return an error in your array
formula.

Third, because you've used named ranges it's hard to tell
if both are the same size. You'll get an NA if they're not.

Sorry if this all pretty basic. It's all I could think of!

Pete

{=sum((((name_type=B$1)*(name_BILL=$A2))
-----Original Message-----
Hi,

Here's an example of what I'm trying to to (the table I
Click to expand...
handle is around 5000 to 10000 records)
Blabla1 BILL1 Atype
Blabla2 BILL1 Btype
Blabla3 BILL2 Atype
Blabla4 BILL1 Atype
Blabla5 BILL2 Btype

I "just" need to summarize in a table on another sheet
Click to expand...
the number of occurences (records) like this
A B C
1 Atype Btype
2 BILL1 occurX occurZ
3 BILL2 occurY occurW

I use the following array formula in B2, for example, and
Click to expand...
I use named range in the data table
{=SUM(IF((name_type=B$1)*(name_BILL=$A2);1;0))}

I really need to avoid pivottable for internal reasons.
Click to expand...
The summary table should be "usable" by anybody in my
company, and none of them know how to use pivots. It is
much easier to show the where the summary table is, and
easier to handle, modify and format (condtions,...)
 
V

vikrant

could you pls elaborate your ques , may be i can give you
perfect ans
-----Original Message-----
Hi,

Here's an example of what I'm trying to to (the table I
Click to expand...
handle is around 5000 to 10000 records)
Blabla1 BILL1 Atype
Blabla2 BILL1 Btype
Blabla3 BILL2 Atype
Blabla4 BILL1 Atype
Blabla5 BILL2 Btype

I "just" need to summarize in a table on another sheet
Click to expand...
the number of occurences (records) like this
A B C
1 Atype Btype
2 BILL1 occurX occurZ
3 BILL2 occurY occurW

I use the following array formula in B2, for example,
Click to expand...
and I use named range in the data table
{=SUM(IF((name_type=B$1)*(name_BILL=$A2);1;0))}

I really need to avoid pivottable for internal reasons.
Click to expand...
The summary table should be "usable" by anybody in my
company, and none of them know how to use pivots. It is
much easier to show the where the summary table is, and
easier to handle, modify and format (condtions,...)
 
Top