Sum

[Ravindra]

Dear All,

I need three different way to Calculate the value for specified person as per their department except first one is given. Kindly see the attached sheet and help me to learn excel.

I will really appreciate your effort.



Thanks....

 

[kchiba]

Hi Ravindra,

You do not give the 3 ways you want to calculate the result, however, look at the attached it uses SUMIF to obtain the results that you have.

kanti

 

[Somendra Misra]

Hi Ravindra,

Please see the attached file. It uses three different ways to get result.

1. SUMIF----> Normal formula.
2. SUM(if( ---> Array formyla require Ctrl+Shift+Enter
3. SUM(IF(ISNUMBER(Match( ---> Array formula Ctrl+Shift+Enter.

But I will suggest you to go for 1 method, as that is fast and does not require CSE, so any user can work with them.

There may be more ways to achieve this.

Regards,

 

[Hui]

=SUMPRODUCT(IFERROR((Sheet1!$B$3:$D$60)*(Sheet1!$A$3:$A$60=$A5)*(Sheet1!$B$2:$D$2=C$3),0)) Ctrl+Shift+Enter

I should also note that i would not setup my data like that
The Month and Auditor should simply be fields and don't separate the separate months, just make a single large table

It makes reporting so much easier

 

[Ravindra]

Thank you so much...