Complicated look up and sum values

[zohaib]

Hello All,

I spend whole day on this and now I your help please. I have attached an example file to this thread.

What I am trying to do is that if Header 1 equals "Out" then look up the value in column 4 and sum them under "Comp 1", "Comp 2" and "Comp 3" respectfully.

I hope this explains what I am trying to do. Please let me know if you have any questions.

Thanks.

 

[AlanSidman]

Not sure what you mean. Suggest you give us a sheet showing expected results. Mock it up manually so we understand exactly what you are looking for.

 

[Hui]

assuming you actually mean this:


B24:
=SUMIFS(Table1[Header 4],Table1[Header 2],$A24,Table1[Header 1],B$23)

Copy across and down

Note the values in A24:A26 have changed from what you posted

I'd also recommend that you use better descriptions for the Field Headers than Header1, Header2 etc

 

[GraH - Guido]

Or use a simple pivot...

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[zohaib]

Hello all,

The numbers I typed in my original spreadsheet (below) should have been what excel calculates also it should not calculate a different number since I am not looking to add "In" and "Out" together.

Comp 1 47
Comp 2 16
Comp 3 5

Narayan,

I made some changes to the spreadsheet you sent (see attached) but is it possible to eliminate header 5 and incorporate that formula into the fields B24 to B26? My original file already has many many columns so I am looking to avoid another column if possible.

Thanks everyone,
Zohaib