Sumproduct Question

[eprice]

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),('Data Sheet'!E$2:E$997*IF('Data Sheet'!E1<=$F$4:$G$4,1,0))


The last part of the formula is the problem, the first two arrays are conditions and the last array is a sum if f4 a period of the year is equal or less to the data sheet tab it is summing.The formula below works fine, but when the last condition is added it doesn't work. Any help is appreciated.

=((SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),'Data Sheet'!E$2:E$997)

[eprice]

I tried the formula variation as the below formula with no results. The condition I am trying to implement is where F4=Current Period As Number, so 2 for February. Then the condition must be less than or equal to the Period established to be summed.Any help is appreciated.

SUMPRODUCT((('Data Sheet'!$A$2:$A$997<2110000)+('Data Sheet'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('Data Sheet'!$B$2:$B$997,{300100,302100},0))),--('Data Sheet'!E1<=$F$4),'Data Sheet'!E$2:E$997)

[NBVC]

Maybe you should post a sample workbook showing your needs. It is difficult to picture your situation.

[DonkeyOte]

You need to rethink the approach given you're trying to multiply dimensions of differing sizes... if you revert to * method you can avoid need for CSE altogether, eg:

=SUMPRODUCT((('DATA SHEET'!$A$2:$A$997<2110000)+('DATA SHEET'!$A$2:$A$997>2119999))*(ISNUMBER(MATCH('DATA SHEET'!$B$2:$B$997,{300100,302100},0)))*('DATA SHEET'!E$2:E$997)*('DATA SHEET'!E1<=$F$4))
(ie confirm with ENTER as normal)

Using Double Unary approach then Array is required...eg:

=SUMPRODUCT((('DATA SHEET'!$A$2:$A$997<2110000)+('DATA SHEET'!$A$2:$A$997>2119999)),--(ISNUMBER(MATCH('DATA SHEET'!$B$2:$B$997,{300100,302100},0))),'DATA SHEET'!E$2:E$997*('DATA SHEET'!E1<=$F$4))
Confirmed with CTRL + SHIFT + ENTER

[eprice]

Here is a sample excel any help would be appreciated


sampleexcel.xls

[NBVC]

Try this in C12:

Please Login or Register  to view this content.

copied across.

This sums only if what is row 1 of Data Sheet is less than or equal to what is in F4 of your Example sheet.

[eprice]

problem sovled thank you very much to everyone

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save