Sumproduct not working

[Curtis]

Why is this formula below not working as is?

If I remove either one of the conditions >=$b$4 or <$b$5 it produces a value
but both conditions together it gives me 0 which is incorrect

Note: both b4 and b5 are '1000 and '1099 respectively


=SUMPRODUCT((ISNUMBER(MATCH('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277,$A12:$B12,0)))*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=$B$4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<$B$5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))

Thanks

 

[Curtis]

Yes they are treated as text by my issue is that each conditions works in
singularity but not together???

Thanks

Do you actually have that little apostrophe thing in there? The '
That will cause the cell to be treated as Text. I don't think that's what
you want. Maybe there are some spaces before or after the 1000 and the 1099.
Sometimes what is in a cell is different than what looks like what's in the
cell. Also, you can use Tools > Formula Auditing > Evaluate Formula to see
how excel derives the result that it derives.

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Why is this formula below not working as is?

If I remove either one of the conditions >=$b$4 or <$b$5 it produces a value
but both conditions together it gives me 0 which is incorrect

Note: both b4 and b5 are '1000 and '1099 respectively


=SUMPRODUCT((ISNUMBER(MATCH('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277,$A12:$B12,0)))*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=$B$4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<$B$5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))

Thanks

 

[ryguy7272]

Do you actually have that little apostrophe thing in there? The '
That will cause the cell to be treated as Text. I don't think that's what
you want. Maybe there are some spaces before or after the 1000 and the 1099.
Sometimes what is in a cell is different than what looks like what's in the
cell. Also, you can use Tools > Formula Auditing > Evaluate Formula to see
how excel derives the result that it derives.

HTH,
Ryan--

 

[Jacob Skaria]

The below works for me with the file name being
YTDTB ACTUAL '09-10.xls

=SUMPRODUCT((ISNUMBER(MATCH('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!
$A$3:$A$277,$A12:$B12,0)))*
('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$D$1:$CI$1>=$B$4)*
('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$D$1:$CI$1<$B$5)*
('[YTDTB ACTUAL ''09-10.xls]YTDTB99'!$D$3:$CI$277))

If this post helps click Yes
---------------
Jacob Skaria

Yes they are treated as text by my issue is that each conditions works in
singularity but not together???

Thanks

Do you actually have that little apostrophe thing in there? The '
That will cause the cell to be treated as Text. I don't think that's what
you want. Maybe there are some spaces before or after the 1000 and the 1099.
Sometimes what is in a cell is different than what looks like what's in the
cell. Also, you can use Tools > Formula Auditing > Evaluate Formula to see
how excel derives the result that it derives.

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Why is this formula below not working as is?

If I remove either one of the conditions >=$b$4 or <$b$5 it produces a value
but both conditions together it gives me 0 which is incorrect

Note: both b4 and b5 are '1000 and '1099 respectively


=SUMPRODUCT((ISNUMBER(MATCH('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$A$3:$A$277,$A12:$B12,0)))*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1>=$B$4)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$1:$CI$1<$B$5)*('[YTDTB ACTUAL
''09-10.xls]YTDTB99'!$D$3:$CI$277))

Thanks