Sumif formula mistake

[sachar]

Dear All,

With reference to the attached sample file, when I apply the following formula in cell “B20” that is not working to avoid the duplication of negative value in the “Total Housing”.



May I know what should I do to get the correct input?

 

[Naresh Yadav]

Hi,

Pls confirm what is the actual value you want based on your current data structure

regards
Naresh

 

[NARAYANK991]

Hi ,

The correct function to use in such situations is the SUBTOTAL function , which ignores nested SUBTOTAL function outputs.

See the attached file.

Narayan

 

[sachar]

Dear Naresh,

Received with thanks.

 

[sachar]

Dear Narayank,

Actually, what I want it will be cleared with the attach a sample file, is there any formula to get the sum of the only negative value of the total rows as I got the answer in cell “B20” by manual calculation.

Thanking you.

 

[John Jairo V]

Hi, to all!

@sachar, this could be and option:
=SUMIFS(B2:B61,B2:B61,"<0",A2:A61,"Total*")

Blessings!

 

[sachar]

Dear John,

Thanks to solve my problem.

 

[Thangavel]

Hi, to all!

@sachar, this could be and option:
=SUMIFS(B2:B61,B2:B61,"<0",A2:A61,"Total*")

Blessings!

Hello, As per my understanding the below formula give the negative value but it double on housing expenses(Electricity bill & Household furnishings and equipment).

=SUMIFS(B2:B19,B2:B19,"<0")

Please clarify my doubt.
Regards,
Thangavel D

 

[sachar]

Dear Thangavel D,

You are absolutely right and I think the above sumifs formula is perfect to avoid the double effect.