# Sum selected values in matrix based on date range as well as unique identifier in row

1. ## Sum selected values in matrix based on date range as well as unique identifier in row

Help please! I'm trying get a formula to apply to a whole matrix data set (as in the attached image) to sum all values in a date range AND a unique identifier for each column, i.e. LVA2

E.g. Sum the LVA1 column values for dates 10/03/2009 to 16/03/2009

Capture_excel.jpg  Register To Reply

2. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

Hi SmanSyd

I would be easier for you to upload a sample xlsx, rather than expect people to replicate all those rows and columns of data   Register To Reply

3. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

Hi and welcome

Please see attached and let me know if this is what you're after

Sum selected smansyd v1.xlsx  Register To Reply

4. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

Thanks so much TheCman81, works perfect!! Exactly what I wanted   Register To Reply

5. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

Update:

I've just tried to apply the formula to a large range (see below) and am getting a '#VALUE!' error. Assuming there is a range max for SUMPRODUCT. Anyone know what I can do to make this work for large dataset?

=SUMPRODUCT(--(A26:A2000>=G2)*(A26:A2000<=H2)*(B25:Z25=I2)*(B26:Z2000))  Register To Reply

6. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row Originally Posted by smansyd =SUMPRODUCT(--(A26:A2000>=G2)*(A26:A2000<=H2)*(B25:Z25=I2)*(B26:Z2000))
If there are any text entries in the sum range B26:Z2000 you'll get the #VALUE! error.

Try this array formula**:

=SUM(IF(A26:A2000>=G2,IF(A26:A2000<=H2,IF(B25:Z25=I2,IF(ISNUMBER(B26:Z2000),B26:Z2000)))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.  Register To Reply

7. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

Many thanks Tony, works a treat!

Thank you both!!!!!!!!!!!!!!  Register To Reply

8. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

You're welcome. Thanks for the feedback! In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.  Register To Reply

9. ## Re: Sum selected values in matrix based on date range as well as unique identifier in row

another
=SUMIFS(INDEX(B2:E21,0,MATCH(I2,B1:E1,0)),A2:A21,">="&G2,A2:A21,"<="&H2)  Register To Reply