Matching a date with multiple instances

Post Reply
Guest

Matching a date with multiple instances

Post by Guest » Sat Dec 10, 2011 2:56 pm

Hi,

 

I am using the following formula in all of the cells of column "Out" of Table A:

 

=IFERROR(INDIRECT("Table B :: B"&MATCH($A2,Table B :: $Date,0)),"")

 

Screen Shot 2011-08-24 at 21.32.01.png

 

The formula looks to match the date in the cell to the left in the column "Date" with a date in column "Date" of Table B:

 

Screen Shot 2011-08-24 at 21.30.50.png

 

If it makes the match it returns the value in the cell to the right of the date in column "Out" of Table B.

 

The problem is that if there is more than one row with the same date (as there is in Table B above) the formula only returns the first value, when I would like it to SUM multiple values if more than one instance of a date occurs in Table B.

 

Is there a way of achieving the result I am looking for, as I can't get my head round other possibilities?

 

Thanks,

 

Nick


Guest

Re:Matching a date with multiple instances

Post by Guest » Sat Dec 10, 2011 3:31 pm

Hi  Nick,

 

Match (and LOOKUP) will use the first match found and return the value associated with that match.

 

Since you want to SUM all the values associated with a certain date, a better choice of function would be SUMIF:

Picture 25.png

 

Column B of the summary table contains the formula below in B2, filled down to the end of the column:

 

=SUMIF(Data :: $A,"="&A2,Data :: B)

 

Column C contains the same formula with a test added to show the result only if the date in column A has been entered into column A of the Data table at least once:

 

=IF(COUNTIF(Data :: $A,"="&A2)>0,SUMIF(Data :: $A,"="&A2,Data :: B),"")

 

Note that a zero still appears in row 10, where the date in blank in the Summary table. This is a legitimate sum, as 'date' is found five times in the data table. The zero can be suppressed by limiting the size of either table to only those rows which contain data.

 

Regards,

Barry


Guest

Re:Matching a date with multiple instances

Post by Guest » Sat Dec 10, 2011 4:33 pm

Thanks Barry.  That works fine. SUMIF seemed the logical function to me when I first started, but I got an error about SUMIF requiring the same number of rows, and so moved on to another function.  Anyway,  the way you have constructed it works fine.

 

One further thing I am wondering about.  Is there an equibavelent function for strings?  I have a third column in the Data table which contains some text (i.e. information about the transaction), and I am wondering if these text strings could be joined in the relevant row of the Summary table.  There doesn't, for instance, appear to be a CONCATENATEIF function.  But perhaps there is another workaround?

 

Nick


Post Reply