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

create formula

A

Ayushnm

New Member
doubt.PNG Sheet1
Column A Column B
d 12
d 344
d 4
d 4
e 4
e 666
e 4444
e 66

Output required in other sheet:
Sheet2
column A Column B
d want total of d which is 12+344+4+4=364
e want total of e which is 4+666+4444+66=5180

Please let me know what formula I should apply.
Currently I am using subtotal in sheet 1 and manually entering the sum in sheet 2...
 
Hi, Enter below Formula in Sheet2 Column B1 and drag it to Down.
I used upto A8 & B8, U Can change the row numbers as much row as u want.
=sumif(Sheet1!$A$1:$A$8,Sheet2!A1,Sheet1!$B$1:$B$8)
 
Thanks a lot nkms143!!!
It saved a lot of time...still facing a small issue..while selecting the range and sum range I only want visble cells sum...Is there any solution for that?
 
I don't want the value 25 marked in blue to be added..I have put a filter and unchecked row 4...but while applying formula it is considering the row and adding it...
output should be 364 not 364+25(389).
doubt1.PNG
 
Hi Ayushnm,

You should upload a sample file with expected output, however, check this {array formula}

=SUM(SUBTOTAL(109,OFFSET(Sheet1!B1,IF(Sheet1!A2:A10=Sheet2!A1,ROW(A2:A10)-1,0),0)))

{array formula needs to be entered with a key combination of
Ctrl+Shift+Enter, not just enter}

Regards,
 
Note : Row 4 is unchecked as I don't want it to be considered while adding..
 

Attachments

  • new.xlsx
    9.7 KB · Views: 4
Thanks a lot guys!!
I am copy pasting the visible cells on to new sheet and applying SUMIF.


Regards
Ayushnm
 
Ayushnm said:
I am copy pasting the visible cells on to new sheet and applying SUMIF.
Click to expand...

One more option since you are using new sheet, I suggest to use a helper column, say column C.

Enter "hide" in row # 4, and fill "unhide" in rest, then use the simple SUMIFS:

=SUMIFS($B$2:$B$10,$A$2:$A$10,G2,$C$2:$C$10,"unhide")

Regards,
 
You must log in or register to reply here.
Back
Top