• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to do YTD summary when having 2 conditions to match in 2 sheets?

Patkica

New Member
Hi everyone!

Please help!

In this FILE, in the sheet "Report", cell R8, I have to sum the amounts from one row (8) in 2 sheets ("Expenses Berlin" and "Expenses Novi Sad").
This should be from Jan 2019 (cell N4) to Mar 2019 (cell O4) (both in different cells so it can be changed - so it has YTD overview), provided that Planned (cell R4) matches Planned (row 4 in the other 2 sheets I mentioned). The dates in the other 2 sheets are in one row (3).

I made this formula:

=SUM(INDEX('Expenses Berlin'!G8:AP8, MATCH(N4 & R4,'Expenses Berlin'!G3:AP3 & 'Expenses Berlin'!G4:AP4, 0)):INDEX('Expenses Berlin'!G8:AP8, MATCH(O4 & R4,'Expenses Berlin'!G3:AP3 & 'Expenses Berlin'!G4:AP4, 0)))+SUM(INDEX('Expenses Novi Sad'!G8:AP8, MATCH(N4 & R4,'Expenses Novi Sad'!G3:AP3 & 'Expenses Novi Sad'!G4:AP4, 0)):INDEX('Expenses Novi Sad'!G8:AP8, MATCH(O4 & R4,'Expenses Novi Sad'!G3:AP3 & 'Expenses Novi Sad'!G4:AP4, 0)))

But even though is giving me some values, it doesn't give the correct ones.

Do you have any ideas how this formula should look like to give me the correct values?

Thank you very much!
 

p45cal

Well-Known Member
This looks pretty difficult due to the layout of your sheets, I'm not sure if I can help, but this is not helped by only being able to view your spreadsheet in your link. If you were able to upload that workbook here it would help a lot, and if it's too big to do that, how about allowing people who have that link to download that workbook to their machine?
It is an Excel workbook and not a google spreadsheet isn't it?
 

Patkica

New Member
Oh, you are right, thanks. I just gave the permission to edit.
It is a Google spreadsheet unfortunately, cause several people have to access it at all times.
Would be wonderful if you can help.
 

p45cal

Well-Known Member
Try, as a start:
Code:
=SUMPRODUCT(('Expenses Berlin'!$G$4:$CD$4=R$4)*('Expenses Berlin'!$G$3:$CD$3>=N$4)*('Expenses Berlin'!$G$3:$CD$3<=O$4),'Expenses Berlin'!$G$8:$CD$8)+SUMPRODUCT(('Expenses Novi Sad'!$G$4:$CD$4=R$4)*('Expenses Novi Sad'!$G$3:$CD$3>=N$4)*('Expenses Novi Sad'!$G$3:$CD$3<=O$4),'Expenses Novi Sad'!$G$8:$CD$8)
 
Last edited:

p45cal

Well-Known Member
Edited the previous message's formula to alter the absolute/relative references, so that it's easier to copy to other cells. It doesn't make any difference to the result.
 

p45cal

Well-Known Member
In cell O1 of the Report sheet put a manual formula which takes the individual cells you want to have summed, something like:
='Expenses Berlin'!G8+'Expenses Berlin'!J8+'Expenses Berlin'!M8+'Expenses Novi Sad'!G8+'Expenses Novi Sad'!J8+'Expenses Novi Sad'!M8
(I may have got your intentions wrong).
Then add the formula in my earlier message into cell R8.
I get the same answers.
You'd better put me right.
 

Patkica

New Member
I know what you mean, but then in April, YTD result wouldn't work.
The formula should work any time I change the dates in O4.
 

p45cal

Well-Known Member
So far as I can see it does sum correctly when you change the date.
However, it messes things up when we're both editing the same workbook at the same time; you added rows above row 8 in the Expenses Novi Sad sheet and the references in the the Report sheet cell R8 no longer point to the correct row.
Is there a way of messaging while editing your sheet?
 
Top