Array Formula Not Working

chet645

Board Regular
Joined
Nov 10, 2010
Messages
67
Office Version
  1. 365
Platform
  1. Windows
I have created a conditional array function that sums values in column K between two dates. When I break the formula up, the individual parts seem to work. However, together I get a #value! reference. The formula is below. Note, the dates in column A are a text string, so I used datevalue and left functions to tease out the date from the text {[string.

{=SUMPRODUCT(($A$4:$A$597<>"")*(DATEVALUE(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)-1))>=$D$1182)*(DATEVALUE(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)-1))<=$E$1182)*K$4:K$597)}

If anyone has ideas on how to get this function to work, I would be very appreciative.

Thanks,

Chet
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
This is a sample of what the dates look like in Column A

02/27/2014 Wed
03/26/2014 Wed
03/25/2014 Tues
03/25/2014 Tues
.
.
.
.


Bryce
 
Upvote 0
This is a sample of what the dates look like in Column A

02/27/2014 Wed
03/26/2014 Wed
03/25/2014 Tues
03/25/2014 Tues
.
.
.
.


Bryce

Just enter, i.e., no need for control+shift+enter:
Rich (BB code):
=SUMPRODUCT(
   K$$4:K$597,
   --(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597))+0>=$D$1182),
   -(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)<=$E$1182))
 
Upvote 0
Just enter, i.e., no need for control+shift+enter:
Rich (BB code):
=SUMPRODUCT(
   K$$4:K$597,
   --(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597))+0>=$D$1182),
   -(LEFT($A$4:$A$597,FIND(" ",$A$4:$A$597)<=$E$1182))


For some reason that doesn't work. here is a sample of how my data is set up.

Column AColumn K
02/27/2014 Wed$3.50
03/26/2014 Wed
03/25/2014 Tue$2.20
03/25/2014 Tue$6.50
03/25/2014 Tue
03/24/2014 Mon$10.75
03/24/2014 Mon

<tbody>
</tbody>

Any thoughts? Really appreciate your help.
 
Upvote 0
"[D]oesn't work" is not very informative. Let's switch to a power formula...

Control+shift+enter, not just enter:
Rich (BB code):
=SUM(
   IF(ISNUMBER(1/(LEFT($A$2:$A$597,FIND(" ",$A$2:$A$597))+0>=$D$1182)),
   IF(ISNUMBER(1/(LEFT($A$2:$A$597,FIND(" ",$A$2:$A$597))+0<=$E$1182)),
    $K$2:$K$597)))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top