Summing Rows and Columns

[Suggie]

Evening all,

I have a row with headers of company names

I have a corresponding column of cost codes that are charged
by these companies.

I need to produce a summary table for all departments.

I've done this using sumif formula but it is messy as I need to get the row headers to be in-line with the summary table.

Is there a way to sum based on a row and column condition - I've been looking a sumproduct but seems to only work on data in columns.

I need the sum of cost returned if the header and cost codes match each other.

Sorry if I'm not explaining very well.

TIA,
Mark

 

[shetlandbob]

hmm,

Example (with dummy data if confidential) would help, but I'm thinking (and I may be wrong) but a pivot table may well be what your after???



Robert Cumming

http://www.consultantengineer.co.uk

 

[Suggie]

Robert - never attached anything before - I can't seem a clip button to show a sample file???

 

[shetlandbob]

Well I was just meaning typing in to excel:

        Company1   Company2  etc.......
Fee1    1000       1000        ......
Fee2    2000       500        ......
Fee3    3000       400        ......
Fee4    4000       200        ......

(this is how I imagined it???)

And an example of what you want to produce? i.e.

           Company1 Company2
Total Fee  10000    2100

Robert Cumming

http://www.consultantengineer.co.uk

 

[Suggie]

OK - see why you need the example

How did you input that table CODE

and I place my exsample up

 

[SilentAiche]

Suggie,

Below the Reply box click on [blue]Process TGML[/blue]. This will provide a list of codes for bold, underlining, code, colors, quotes, etc.

Be sure to look at [TT] to help maintain text alignment.

Also,

I need the sum of cost returned if the header and cost codes match each other.

This part of your post was a bit confusing. When you get a chance, can you explain a bit further as to how they match?

HTH,
Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]

 

[Suggie]

Summary Table

Cost Code Company A Company B Company C Company D Company E Company F
1
2
3
4
5

Data Source A

Cost Code Company A Company D Company C Company F
1 50 108 50 200
2 70 22 90 40
3 20 50 80 20

Data Source B

Cost Code Company D Company E
4 $70 $22
1 $20 $50
2 $90 $40
5 $50 $200

 

[Suggie]

Didn't mean to subit post...one of those days.

I need to populate the summary table with the details from
the below data tables, I was trying sumif but this didn't work as the headers do not line up e.g. Company D in data source b does not line up with Company A in the summary table.

Anyway - I think I may have figured it out using this formula

=SUMPRODUCT((B14:B16=B5)*(C13:F13=C4)*C14:F16)


I need to bring back the sum if the company name in the row header matches the cost code number

TIA but I think the sumproduct will work - just need to figure out how to return "" if the cell is blank. Will try out a few different things.


Apologies for my vague question.

Thanks,
Mark

 

[shetlandbob]

Have you figured it out?

For reference type
[ignore]

 This is my Code

[/ignore] to get

This is my Code

Click on Process TGML to find out all the wasys of formatting your text. You 'll also find this link below the text input page


Robert Cumming

http://www.consultantengineer.co.uk

 

[Suggie]

I have thanks - first time I've used sumproduct - excellent function.

Thanks for bearing with me.

Mark