Summing values based on header rows and columns

[Queenie80]

Hello everyone,

I'm sure there is an easy solution to this but I just can't find what I need, can anyone help me please?!

I have a table which contains headers of the same title RED and GREEN, as shown below (except the APPLES and GRAPES cells are merged with the cell on their right to give the effect of two columns which have then been split into sub-columns of RED and GREEN).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]APPLES[/TD]
[TD][/TD]
[TD]GRAPES[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COUNTRY[/TD]
[TD]RED[/TD]
[TD]GREEN[/TD]
[TD]RED[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]UK[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula which will sum the number values based on the COUNTRY and RED headers.
For example, the UK should give an answer of 4 as it has 1 RED APPLE and 3 RED GRAPES.

My full working table has 10 columns which I need to sum, and though the standard SUMIF formulas work, they take up rows and rows of the formula line!
Is there a formula I can use instead which is more efficient?

Many thanks in advance.

 

[grd]

Don't understand why SUMIF doesn't work and why it takes up rows and rows.

Assuming your example starts in A1 and you insert Red in F2 the following formula can go in F3 to give the answer to your example.
=SUMIF($B$2:$E$2,F$2,B3)

I've used some absolute references to allow you to copy along and down for more options

 

[shyy]

Just an advice,

If you can don't use the merge tool but center across selection under format. using formulas that deals with a cell that is merged can be nasty.

 

[Queenie80]

Don't understand why SUMIF doesn't work and why it takes up rows and rows.

Assuming your example starts in A1 and you insert Red in F2 the following formula can go in F3 to give the answer to your example.
=SUMIF($B$2:$E$2,F$2,B3)

I've used some absolute references to allow you to copy along and down for more options

Many thanks for your quick reply.
Ok, so I can see that your formula works, but is there no way I can reference the country itself within the formula?
The formula will be in a different tab of my workbook, in which case being able to reference the country within the formula would be less risky than referencing B3.

I currently have =SUMIF(Fruit!$A$3:$A$38,'Data Sheet'!$A29,Fruit!$B$3:$B$38)+SUMIF(Fruit!$A$3:$A$38,'Data Sheet'!$A29,Fruit!$F$3:$F$38)+SUM.... continued another 8 times in order to sum the total 10 columns. Not pretty!

Thanks,

 

[grd]

Not totally sure why this is a problem.

If you are sure the answer contains the data for the UK, for example, how would you know the answer itself is where it should be?

What are you totalling against each country?