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

Sum formula with different criterias

uday

Member
Hi team,

I need total sum of WD1 for BSC ,cleaning and pest in B2,B3,B4 cells. Same way this could also applied for WD2 and WD3 cells.
The data tab is Sheet2. File is attached.

Regards,
Uday
 

Attachments

  • Sample1.xlsx
    11.1 KB · Views: 9
vEFGH
1WD1WD2WD3
2BSC656559129557992
3Cleaning513614434642287
4Pest206911374213867

vEFGH
1WD1WD2WD3
2BSC=SUMIF($A$2:$A$29,E2,$B$2:$B$29)=SUMIF(A$2:A$29,E2,C$2:C$29)=SUMIF(A$2:A$29,E2,D$2:D$29)
3Cleaning=SUMIF($A$2:$A$29,E3,$B$2:$B$29)=SUMIF(A$2:A$29,E3,C$2:C$29)=SUMIF(A$2:A$29,E3,D$2:D$29)
4Pest=SUMIF($A$2:$A$29,E4,$B$2:$B$29)=SUMIF(A$2:A$29,E4,C$2:C$29)=SUMIF(A$2:A$29,E4,D$2:D$29)
 
Same formula, slightly different appearance
= SUMIFS( Table1[WD1], Table1[items], distinctItems )
I normally set out to create the entire table as one spilt array but in this instance it was only possible to do it column by column.
The row headers 'distinctItems' were calculated using the modern array formula
= UNIQUE(Table1[items])
It is good to have new toys to play with!

62605
 
Same formula, slightly different appearance
= SUMIFS( Table1[WD1], Table1[items], distinctItems )
I normally set out to create the entire table as one spilt array but in this instance it was only possible to do it column by column.
The row headers 'distinctItems' were calculated using the modern array formula
= UNIQUE(Table1[items])
It is good to have new toys to play with!

View attachment 62605
Could you please attach a sample file for sumifs function too to practise. thank you
 
I have attached the file can you please apply in attached file?
 

Attachments

  • Sample1 (1).xlsx
    12.3 KB · Views: 2
Back
Top