# 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

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

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

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

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))

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.

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!!!!!!!!!!!!!!

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.

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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1