"SUMIFS" from table with sort cell

[eddor]

Hi All Master, Greeting from Indonesia


I am Edo, after a long time become silent user fan MrExcel today I joined in this forum and need to share some issue that I can't find the similar issue from any thread.

I have an excel table and some formula for sum as below :

=IF(SortOrder2="Rev SFDC",SUMIFS($J$11:$J$970,$E$11:$E$970,$I$2,$G$11:$G$970,J1),SUMIFS($L$11:$L$970,$E$11:$E$970,$I$2,$G$11:$G$970,J1))

When I sort some column ( ex- column PP with sort only for "A" value ), the SUMIFS formula above not sum correctly due to the sort process just hide another line without "A" value.

What the right formula for sort in the same result even user sort some column

JUL

AUG

SEP

Q2

A

2,127

4,517

0

6,644

B

1,323

5,728

0

7,051

C

0

6,000

0

6,000

D

0

0

0

0

T

0

0

0

0

S

0

0

0

0

Forcast

3,450

16,245

0

19,695

Rev SFDC

ID

Sales

Project Name

End Customer

PP

ECD

ECD MTH

MIS BT

BT FCST

Rev SFDC

GM SFDC

Rev OMS

GM OMS

0066F00000qxOZt

Andi

Security Gate

PT. Jaya Abadi

B

8/31/2018

AUG

Unconfirmed

122

41

422

41

0066F00000grRQC

Anwar

Data Center

Lembaga Data

B

8/10/2018

AUG

Unconfirmed

3,000

900

0

0

0066F00000o88IC

Anwar

DRC

Lembaga Data

A

8/9/2018

AUG

Unconfirmed

453

63

793

63

0066F00000o8F5w

Anwar

Infrastructure Enhancment-II

CV Avanza

A

12/8/2018

DEC

Unconfirmed

894

200

0

0

0066F00000mnKMM

Danny Rosdian

Enterprise Oracle

INDO LELE

A

8/16/2018

AUG

Unconfirmed

4,064

800

0

0

0066F00000jb9aJ

Desiree

MIEKOCOK EXTENDED

TOKO KLENTONG

B

7/31/2018

JUL

Unconfirmed

112

132

750

150

0062000000Tmut6

Desiree

MIEKOCOK

TOKO KLENTONG

B

7/31/2018

JUL

Unconfirmed

279

95

0

0

0066F00000igSKJ

Virgia

BIG DATA

HUTAN RIMBA, LTD

B

8/16/2018

AUG

Unconfirmed

1,300

501

0

0

0066F00000moEHN

Virgia

CCTV

HUTAN RIMBA, LTD

B

8/16/2018

AUG

Unconfirmed

1,306

860

1,306

912

0066F00000gF0a2

LIM

ATS Exalogic Elastic Cloud Phase 1

LELEKOM

A

7/31/2018

JUL

Oracle

MAY

1,837

195

1,033

205

0066F00000p2EEY

LIM

ATS Exalogic Elastic Cloud Phase 2

LELEKOM

B

7/31/2018

JUL

Unconfirmed

931

121

631

121

0066F00000p2EFC

LIM

ATS Exalogic Elastic Cloud Phase 3

LELEKOM CELL

A

7/31/2018

JUL

Unconfirmed

290

53

590

53

0066F00000qxPxY

LIM

Oracle Manage Operation

LELEKOM CELL

C

8/31/2018

AUG

Unconfirmed

6,000

2,880

0

0

<tbody>
</tbody>

<link rel="File-List" href="<a href=" file:="" c:="" users="" eddor="" desktop="" sum%20temp_files="" filelist.xml"="" target="_blank"><link id="shLink" href="<a href=" file:="" c:="" users="" eddor="" desktop="" sum%20temp_files="" sheet001.htm"="" target="_blank"><link id="shLink">

 

[eddor]

UP....UP.....UP.....