Sumif Help

[Manuel998]

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!

 

[Debaser]

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?

 

[Manuel998]

any other options i could use?

 

[ArmstrongArcade]

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),
...)

 

[vletm]

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

 

[Manuel998]

@vletm - Thanks i think it just needs a slight tweak because what i am trying to achieve is in column C5 instead of 99999 it should read 100349.

 

[vletm]

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! )

 

[Manuel998]

@vletm I added the data in sheet 1 typed the =zzz() however i get a #name error.

 

[vletm]

Send Your file after You've tested this file.

 

[Manuel998]

still getting the same error spreadsheet attached.

 

[vletm]

I opened above file and it looks like below...

Are Macros Enabled?
Those have to be!

 

[Manuel998]

@vletm thanks this worked!

 

[ArmstrongArcade]

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)