Sumproduct

S

sgm020

Hi,

I have a list like below:
DATES VALUES
01.01.2004 100
05.05.2004 200
01.10.2004 300
01.03.2005 400
01.04.2005 QQQ(text)
10.07.2005 600
08.03.2006 (Blank)
01.04.2006 800
15.05.2006 900
(Blank)(Blank)
............... ........

I want to calculate between

Start date 01.01.2005
End date 31.12.2005

by the =SUMPRODUCT((A2:A20>=D22)*(A2:A20<=D23)*(B2:B20)) formula, bu
this formula gives #VALUE! result.

How I can solve this problem.

Thanks
 
S

Stefi

=SUMPRODUCT(--(A2:A10>D22),--(A2:A10<D23),B2:B10)


Regards,
Stefi

„sgm020†ezt írta:
 
B

bplumhoff

Hello,

You can enter as array formula:
=SUMPRODUCT(--(A2:A20>=D22),--(A2:A20<=D23),IF(ISERROR(--B2:B20),0,--B2:B20))
(CTRL + SHIFT + ENTER)

HTH,
Bernd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumproduct with unhidden data 1
Locating values in a range and bringing up the corresponding price 3
Conditional Minimum formula 3
Finding the corresponding value in a range? 4
Is there a way to use a multiple column array in SUMPRODUCT? 2
Adding values across a row based on a corresponding column value 9
sumproduct, multiple if, some same cell, determine number of rows 4
Sum data with criteria stored in row and column 3

Top