• 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.

Sumif Help

M

Manuel998

Member
I am trying to sumif in sheet 2 and my sum range is Sheet 1 I23:AH688. the first criteria is in sheet 2 A5 and the criteria range is in sheet 1 range D22:D688. The second criteria is in sheet 2 C2 and the criteria range is in sheet 1 range D22:D688. how can i write a sumif formula that will work here?

Thanks for your help!
 

Attachments

You can't because your sum range and criteria ranges are not the same size and shape. If you have them available, you can use SUM and FILTER?
 
Hey Manuel,

Problem 1, Fixed Assets (Sheet2, A5):
The reason why there is no summation here is because there are no numbers associated with the Row that contains Fixed Assets. In the attached, I added Fixed Assets in Sheet1 Column C22:C40, where Fixed Assets seemed to be appropriate. I know you had other data there (FA_A), for the SUMIFS function to work properly, each row you want to sum must have Fixed Assets associated with it.

With Fixed Assets added to Sheet1 C22:C40:
=SUMPRODUCT((Sheet1!$C22:$C688=Sheet2!$A5)*(Sheet1!$I22:$AH688))

Problem 2, Deferred Tax (Sheet2, C2):
Regarding Deferred Tax at 01 Apr 2024, I used the same SUMPRODUCT method:
=SUMPRODUCT((Sheet1!$D$22:$D$688=Sheet2!$C2)*(Sheet1!$I$22:$AH$688))

You can also SUM(SUMIFS()), but given the total Rows-26, that would get rather lengthy. I shortened that option, but here's an example:
=SUM(
SUMIFS(Sheet1!$J:$J, Sheet1!$D:$D,Sheet2!$A5),
SUMIFS(Sheet1!$K:$K, Sheet1!$D:$D,Sheet2!$A5),
SUMIFS(Sheet1!$L:$L, Sheet1!$D:$D,Sheet2!$A5),
...)
 

Attachments

Sheet1
# add new column left side of 'Deferred tax assets'- column
# add numbers for each Sheet2-'group' ( I added some of those )
Sheet2
# add unique numbers for each A-column values eg to B-column
>> use Sumifs-function (sample in cell C5)
>> Later, You could hide those new columns
 

Attachments

Could You add more values to Sheet1 and valid results to Sheet2?
... that could figure Your needs.
Hmm?
You can test this sample.
After You've added more values to Sheet1
and
with Sheet2 write =zzz() to cells which You want to solve
( Note: copy & paste is different than write! )
 

Attachments

Last edited:
Threads ded, but I think I finally got it.
How do I do a proper box?
=IFERROR(
SUM(
INDEX(Sheet1!$H:$AH,MATCH(Sheet2!$A5,Sheet1!$D:$D,0),0):
INDEX(Sheet1!$H:$AH,MATCH(Sheet2!$A6,Sheet1!$D:$D,0),0)),
0)
 

Attachments

You must log in or register to reply here.
Back
Top