sumproduct multiple criteria

[Maggie]

Hi,

Trying to modify this formula to calculate using data in B:K

Dates in AL2 and AL3
Criteria in B1 and B2
Total data points in G9

It calculates percentages correctly for data in column B while ignoring columns C:K

SUMPRODUCT((N(OFFSET(INDIRECT("'"&$AR$1&"'!A2:A2923"),ROW(INDIRECT("1:2923"))-1,,))>=$AL$2)*(N(OFFSET(INDIRECT("'"&$AR$1&"'!A2:A2923"),ROW(INDIRECT("1:2923"))-1,,))<=$AL$3)*(N(OFFSET(INDIRECT("'"&$AR$1&"'!B2:K2923"),ROW(INDIRECT("1:2923"))-1,,))>=$B$1)*((N(OFFSET(INDIRECT("'"&$AR$1&"'!B2:K2923"),ROW(INDIRECT("1:2923"))-1,,))<=$B$2)))/$G9

Thanks

 

[Sajan]

Hi Maggie,
Welcome to the forum!

It might be better if we help with the problem you are looking to solve, instead of repairing the formula above. Towards that end, please describe what you are trying to do, and we might be able to offer suggestions or solutions.

Also, it usually helps to have a sample workbook to visualize your problem / question. You can upload files by clicking on the "Upload a File" button. (Remember to remove anything sensitive in your sample data before posting.)

Cheers,
Sajan.

 

[Debraj]

Hi Maggie,
Welcome to the forum.

Working fine.. in my sheet....
Just added a "=" in front of the formula..

Please spend few time to read the below..
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Please then come back, with, all the required data, to get speedy answer..

 

[Maggie]

Hi,

The equal sign is in front of my formula

File will not upload 2818KB

 

[Maggie]

Deleted more sheets

 

[Sajan]

Hi Maggie,
Can you describe what you are attempting with the following segment? (I shortened the range to 100 to debug)
N(OFFSET(INDIRECT("'"&$R$1:$Y$1&"'!A2:A100"),ROW(INDIRECT("1:100"))-1,,))

Can you describe the problem you are looking to solve overall?

-Sajan.

 

[Maggie]

Hi,

Use 367:397 to shorten the range (Jan 07)

Using sheet seventh (X1), calculate the % of data points that fall within a specified date (R2:R3) and min/max numbers (B2:B3, C2:C3, etc). The formulas that work calculate when looking at a single column. When I try to calculate using columns B:K it only calculates for B.

On sheet seventh
B383 -50
C383 -150
J383 - 150
K383 - 50

On sheet 2007
B9 should return 50% (2 of the 4 numbers between 1-99)
C9 should return 50% (2 of the 4 numbers between 100-199)

The All formulas will calculate using all of the data points in the 9 sheets.