Sum - Multiple Criteria

[Spoon2322]

Hi All,

How do I get the values in col H, I & J from table A into the table below Col J, by Project header, Project Activity code and Period Number please? I think it is some form of sumifs statement, but nothing I do is working. Hoping someone can advise please. Thank you so much in advance! Suzanne

 

[jasonb75]

Maybe

=SUMIFS(INDEX($I$7:$K$12,0,MATCH($H17,$I$6:$K$6,0),$G$7:$G$12,$D16,$H$7:$H$12,$G16)

Note that I haven't included the year in the criteria.

 

[Spoon2322]

Maybe

=SUMIFS(INDEX($I$7:$K$12,0,MATCH($H17,$I$6:$K$6,0),$G$7:$G$12,$D16,$H$7:$H$12,$G16)

Note that I haven't included the year in the criteria.

Thank you Jason for responding. I will have to account for the change in financial year also. My budget goes all the way out to 2037. I must admit to being frustrated, I thought I was ok with excel, but this has really got me flumoxed!

 

[jasonb75]

I will have to account for the change in financial year also.

If that needs to be included then you will either need to change the years in one of the tables so that they are consistent in format (i.e. change the year in the bottom table to 2020/21 or the top table to 2020), or we will need to specify in the formula where the fiscal year changes over in order to avoid any potential errors.

Noting that if using the latter option, the formula will be more complex (less efficient) and that it would be open to interpretation if it is not just you filling out the sheet.

Also, please update your profile by clicking your user name at the top right of the page, then going to 'Account Details' and choose the version of excel and platform that you are using from the options that are available so that we know which functions / features can be used in the answer. If you use multiple versions please select the oldest one that you need to use it with. After ticking the boxes you will need to scroll to the bottom and save the changes.

 

[Spoon2322]

If that needs to be included then you will either need to change the years in one of the tables so that they are consistent in format (i.e. change the year in the bottom table to 2020/21 or the top table to 2020), or we will need to specify in the formula where the fiscal year changes over in order to avoid any potential errors.

Noting that if using the latter option, the formula will be more complex (less efficient) and that it would be open to interpretation if it is not just you filling out the sheet.

Also, please update your profile by clicking your user name at the top right of the page, then going to 'Account Details' and choose the version of excel and platform that you are using from the options that are available so that we know which functions / features can be used in the answer. If you use multiple versions please select the oldest one that you need to use it with. After ticking the boxes you will need to scroll to the bottom and save the changes.

Will do - thanks Jason

 

[jasonb75]

I notice that you updated your profile with platform, but you missed the excel version

Once you decide which option you want to use in respect of changing the year format for one of the tables, I'll be able to update the formula for you.
They will both be quite different from the current one, but one more so than the other.

 

[Spoon2322]

If that needs to be included then you will either need to change the years in one of the tables so that they are consistent in format (i.e. change the year in the bottom table to 2020/21 or the top table to 2020), or we will need to specify in the formula where the fiscal year changes over in order to avoid any potential errors.

Noting that if using the latter option, the formula will be more complex (less efficient) and that it would be open to interpretation if it is not just you filling out the sheet.

Also, please update your profile by clicking your user name at the top right of the page, then going to 'Account Details' and choose the version of excel and platform that you are using from the options that are available so that we know which functions / features can be used in the answer. If you use multiple versions please select the oldest one that you need to use it with. After ticking the boxes you will need to scroll to the bottom and save the changes.

Hi Jason,

I have change the cols in Tab 2 to reflect the correct formats now. Are you able to continue to assist please? I appreciate your time.

Apologies I cannot download the Add in to convert as I am on a works laptop and I am not allowed.

Many thanks

Suzanne

 

[Spoon2322]

I notice that you updated your profile with platform, but you missed the excel version

Once you decide which option you want to use in respect of changing the year format for one of the tables, I'll be able to update the formula for you.
They will both be quite different from the current one, but one more so than the other.

Done! 2019 - and thank you ?

 

[jasonb75]

Thanks for the additional info.

This should do what you need, although you might have to add some more criteria pairs to the end of SUMIFS if needed, I missed the Job Number column previously so assume that it will also be required as a criteria. I've added that one to the formula but may have missed others if the headers are named differently.

This should work with the layout of your second screen capture, although as you were not able to use the add-in to post live data I have not tested it on anything.

=SUMIFS(INDEX($I$7:$L$14,0,MATCH(2,1/($H20=$I$6:$L$6)/($I20=$I$4:$L$4)),$G$7:$G$14,$D20,$H$7:$H$14,$G20,$E$7:$E$14,$C20)

 

[Spoon2322]

Thanks for the additional info.

This should do what you need, although you might have to add some more criteria pairs to the end of SUMIFS if needed, I missed the Job Number column previously so assume that it will also be required as a criteria. I've added that one to the formula but may have missed others if the headers are named differently.

This should work with the layout of your second screen capture, although as you were not able to use the add-in to post live data I have not tested it on anything.

=SUMIFS(INDEX($I$7:$L$14,0,MATCH(2,1/($H20=$I$6:$L$6)/($I20=$I$4:$L$4)),$G$7:$G$14,$D20,$H$7:$H$14,$G20,$E$7:$E$14,$C20)

Thanks Jason - i will give this a go. If i have any questions, do you mind if i come back to you? I appreciate your time is valuable. Suzanne