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

**smansyd** · 07-08-2014, 03:00 AM

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

Thanks in advance!

**dubcap01** · 07-08-2014, 03:47 AM

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

**TheCman81** · 07-08-2014, 04:36 AM

Hi and welcome

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

Sum selected smansyd v1.xlsx

**smansyd** · 07-08-2014, 08:25 PM

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

**smansyd** · 07-08-2014, 09:30 PM

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

**Tony Valko** · 07-08-2014, 09:43 PM

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.

**smansyd** · 07-09-2014, 12:33 AM

Many thanks Tony, works a treat!

Thank you both!!!!!!!!!!!!!!

**Tony Valko** · 07-09-2014, 07:47 PM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

**martindwilson** · 07-09-2014, 07:56 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Training Matrix to display colors based off of selected values from a dropdown menu.

[SOLVED] Copying the details of a code based on their unique identifier.

[SOLVED] add increment identifier to unique values

[SOLVED] Counting a column of numbers based on a unique identifier

[SOLVED] Take column data from one sheet to another based on unique identifier

Bookmarks

Bookmarks

Posting Permissions