excel help in understanding a calculation

anshuma
anshuma used Ask the Experts™
on
Hi,

Someone just gave me an 80MB excel with lots of calculations and when I look at the calculation it looks like this

=(SUMIFS(RawData!H:H,RawData!$A:$A,A$1,RawData!$C:$C,$Q4)-SUMIFS(RawData!$L:$L,RawData!$A:$A,A$1,RawData!$C:$C,$Q4))/SUMIF(RawData!$A:$A,$A$1,RawData!$L:$L)

Can someone please tell me what that means as I am not used to these cryptic calculations

thanks
-anshu
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Commented:
It's in 3 parts so think (A-B)/C

Calculation A is summing values in column H when:
- anything in column A is equal to whatever value is in cell A1.
- anything in column C is equal to whatever value is in vell Q4.

Calculation B is summing values in column L when:
- anything in column A is equal to whatever value is in cell A1.
- anything in column C is equal to whatever value is in cell Q4.

Calculation C is summing values in column L when anything in column A is equal to the value in A1.

Given the limited information you provided, this is as much as I can understand. If you provide the names/titles of the columns in the formula (column A, L, Q, C) I "might" give a better answer.
Saurabh Singh Teotia
Top Expert 2015
Commented:
anshu,

Sumifs and Sumif formula are two formulas given in excel which gives you the sum of the range when particular condition is met..

Now in Sumifs(the first range is the range of which you need the sum,criteria1 range,crietria1,criteria2 range,criteria2, ,criteria 3 range,criteria3.. etc etc)

What this essentially does it will give you the sum of the first range which you mentioned where all the criteria's which you specified are met. It's basically applying filter to the range basis of your criteria and give you the sum of the subset range where all the criteria are meet.. Note the filter which is applied is AND that is basically all the criteria's should met..

Similarly Sumif only works with 1 criteria and that is the difference between sumif and sumifs that sumifs can work on multiple criteria and sumif will work only in 1..

SUMIF formula goes like this...

=sumif(criteria range,criteria to check,range to sum)

Hope this helps providing you the formula understanding...

Further details provided by Microsoft on this Link

Saurabh...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial