Vlookup & Sum based on a Criteria

Hi All,

I'm trying to collate a report & base on the month I need to sum all the transactions. Please find attached my working file . I have used SUM & offset function & it works , but when a field is missing from the data file it does not work.

Is there a simpler or other formula I could work on .



Thanks in advance
John

Perhaps use a Pivot Table?

Pivot table does not help. I have used Sum & offset formulae , but the changllege is when a loarge data we cannot keep change the cell reference.

We need to lookup & & then sum

any thoughts

What is wrong with a Pivot Table? Have you tried it ?

Hi , Pivot will work, however I have multiple other data being extracted & consolidated in the a report with multiple conditions.

Maybe Sumproduct?

Hi,

Why not use sumproduct? This way you wont even need the index tab.

=SUMPRODUCT(('data dump'!$G$15:$G$19=E8)*('data dump'!$H$12:$S$12<=$J$1)*('data dump'!$H$15:$S$19))

One thing you should do for this formula to work is to change the months in the data dump sheet to actual dates instead of text. If you still want it to show only month you can then go to format cell and under custom put it as mmmm.

View attachment Test.xlsx

Thank you for help & will try to change the format of month.

thanks a ton Cheers John

Please,do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).