I hope I can explain without a picture.

My associates are big on using sumproduct to gleen specific information say
by line = location and column = category.

Sumproduct is use like this:
"SUMPRODUCT((DATA!$A$3:$A$6288=$B1317)*(DATA!$B$3: $B$6288=N$2)*(DATA!$C$3:$C$6288))".

This is great except you have no flexiblility to build a new report that
could be complicted. Also the data comes out for multiple periods, same
category in different colums which requires me to cut and paste to meet the
above criteria.

Is there a different function that may work better?

Maybe take a look at pivot tables?

On Dec 19, 2:16 pm, asg2307 wrote:
I hope I can explain without a picture.

My associates are big on using sumproduct to gleen specific information say
by line = location and column = category.

Sumproduct is use like this:
"SUMPRODUCT((DATA!$A$3:$A$6288=$B1317)*(DATA!$B$3: $B$6288=N$2)*(DATA!$C$3:$-C$6288))".

This is great except you have no flexiblility to build a new report that
could be complicted. Also the data comes out for multiple periods, same
category in different colums which requires me to cut and paste to meet the
above criteria.

Is there a different function that may work better?

How about a few more sample formulas

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"asg2307" wrote in message
...
I hope I can explain without a picture.

My associates are big on using sumproduct to gleen specific information
say
by line = location and column = category.

Sumproduct is use like this:
"SUMPRODUCT((DATA!$A$3:$A$6288=$B1317)*(DATA!$B$3: $B$6288=N$2)*(DATA!$C$3:$C$6288))".

This is great except you have no flexiblility to build a new report that
could be complicted. Also the data comes out for multiple periods, same
category in different colums which requires me to cut and paste to meet
the
above criteria.

Is there a different function that may work better?

No other formula will give you the flexibility you seem to desire, they all
assume some structure to the data.

You would be better spending your time on organising the data into a format
that facilitates useful querying.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"asg2307" wrote in message
...
I hope I can explain without a picture.

My associates are big on using sumproduct to gleen specific information
say
by line = location and column = category.

Sumproduct is use like this:
"SUMPRODUCT((DATA!$A$3:$A$6288=$B1317)*(DATA!$B$3: $B$6288=N$2)*(DATA!$C$3:$C$6288))".

This is great except you have no flexiblility to build a new report that
could be complicted. Also the data comes out for multiple periods, same
category in different colums which requires me to cut and paste to meet
the
above criteria.

Is there a different function that may work better?