Summing Multiple Criteria with moving date column

[dmang1]

Hello,

I need to add together all relevant line items for the changing month based on a number of criteria. I have tried a combination of SUMIFS, HLOOKUP, MATCH and INDEX to achieve this but to no avail.

I have attached the bugger so you can see for yourself the pickle I'm in. Any help would be appreciated.

Regards,
Diana

[jeffreybrown]

Hi Diana,

Do you have a sample workbook to post?

[dmang1]

Thanks for your quick response. I thought I had uploaded my example workbook.

Sorry, this is my first time seeking help on this forum.

Cheers,
Diana

[jeffreybrown]

Hi Diana,

On the Summary tab, what is the answer for row 4? Also on the Summary tab, is C1 to be match against the EF tab row 1 or column C or both?

[dmang1]

Hi Jeff,

1. The answer for row 4 should be $28,015.70 because we want the answer to come from the May column in the EF tab.
2. If the date on the Summary sheet is changed, the formula should then sum up the values from the corresponding columnn in the EF tab according to the appropriate criteria on the left in the Summary sheet.

I hope this makes sense.

Diana

[jeffreybrown]

Try in C3 copied down

Please Login or Register  to view this content.

[dmang1]

Thanks Jeff.

That works a treat in the example workbook but when I substitute it into my workbook and make the appropriate reference changes, I get #VALUE errors. Any ideas why?

Diana

[dmang1]

Sorry Jeff,

Just worked it out myself. The #VALUE Error was occurring because the named range I was using included the date row.

Thanks again.

Diana

[jeffreybrown]

Hi Diana,

My first thought, make sure all of the ranges are the same sizes.

[dmang1]

Hello Jeff,

Sorry to bother you again, but the results are coming out as all 0's now. Argh!

Diana

[jeffreybrown]

The file you are working on, is it possible to post?

If you don't want to post it, would you want to email it?

[dmang1]

It's ok now Jeff.

I got it working by going back to the example workbook and looking over the formula again. I had added an additional criteria that didn't work in the example workbook either. All is good in my world now. Thanks for your good deed for the day!

Cheers,
Diana

[jeffreybrown]

You are most welcome and now off to bed for me. Glad you got it working and thanks for the feedback.