INDEX/MATCH: how to prevent #N/A error

[zusammen]

Hi Valued Forum Members,

I have a situation in which I want to SUM several costs in separate columns according to the date to which they correspond.

(see attached sheet with dummy example)

eg: any cost falling on April 3, sum them and give the total amount.

I can achieve this using iterations of the following formula:

=SUM(INDEX($L$7:M37,MATCH($F7,$L$7:$L$37,0),2),(INDEX($O$7:$P$37,MATCH($F7,$O$7:$O$37,0),2),($C7,$D7,$E7)))

My problem is that if the date range in columns L or O don't fall within the range in column F (which will always be FIXED), the formula returns #N/A.

Is there any way in which I can still add all the values, but override the error if the dates don't happen to (always) match?

Regards,

Z.

[ConneXionLost]

Hi Z,

Try this:

=IF(OR(ISNA(MATCH($F7,$L$7:$L$37,0)),ISNA(MATCH($F7,$L$7:$L$37,0))),"",SUM(INDEX($L$7:M37,MATCH($F7,$L$7:$L$37,0),2),SUM(INDEX($O$7:$P$37,MATCH($F7,$L$7:$L$37,0),2),($C7,$D7,$E7))))

Cheers,

[daddylonglegs]

Simpler to use SUMIF, I think. Try this formula in I7

=SUMIF(L$7:O$37,H7,M$7:P$37)+SUM(C7:E7)

format as general and copy down

[zusammen]

Thanks Guys. Much appreciated.

FYI: I used the SUMIF solution and it works a treat.

Regards,

Z.