View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
John C[_2_] John C[_2_] is offline
external usenet poster
 
Posts: 1,358
Default sumproduct formula

Are your time/date stamps in Data 14 tab formatted as text? And do they have
both date and time in the same format as NOW().
You could try this formula:
=SUMPRODUCT(--('Data 14'!$C$8:$C$6533="InAlm"),--('Data
14'!$D$8:$D$6533=B1),--(--('Data 14'!$A$8:$A$6533)(NOW()-1)))

--
John C


"computers hate me" wrote:

i want to calculate how many alarms went off in the past 24 hours
therefore i am using the sum product formula

=SUMPRODUCT(--('Data 14'!$C$8:$C$6533="InAlm"),--('Data
14'!$D$8:$D$6533=B1),--('Data 14'!$A$8:$A$6533(NOW()-1)))

The formula gives me back how many alarms came back based on three different
criteria.

the formula without the last part works just fine but then when i put in the
('Data 14'!$A$8:$A$6533(NOW()-1) it gives me all 0's.