Something better than Sumproduct
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? |
Something better than Sumproduct
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? |
Something better than Sumproduct
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? |
All times are GMT +1. The time now is 06:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com