Excel Calculations

marka87uk

Refering to the following image (although I have OOo at home, I would like to solve this for Excel (for work)).



Basically, I need to populate the table on the right with information from the left.

This may be a bit confusing but I'll try my best to explain...

1. For instance, in F2, the value should be the number of the amounts which Fred had for reason 1. It would be 1 because Fred only has one reason 1. In F4 the value should be 2 because Peter has two reason 1's.

2. Secondly I need to calculate the total cost for that reason, F6 would read £255 (100+25+130), as that is the total cost for reason 1. G6 would read £70 (20+50).

Is this possible?

village_life

in case 1 - do a count if statement

for case 2 - do a simif statement

use the excel wizards to help you out - it should be quite straight forwards (i also think a pivottable might do this quite nicely)

let me know if u need any further help

Chippy_Minton

Use SUMPRODUCT as follows:

1. In F2: =SUMPRODUCT(($B$3:$B$9="Fred")*($C$3:$C$9=1))

2. In F6: =SUMPRODUCT($A$3:$A$9*($C$3:$C$9=1))

I've used absolute references for the cell ranges (e.g. $B$3:$B$9) because your data has a fixed range and this makes it easier to drag the formula down or across. You can change this easily by clicking the mouse inside a range on the formula bar and pressing F4 to cycle through relative, mixed and absolute references.

marka87uk

Thanks I shall try it 2moro.

Fairwinds

This is how I'd do it -

In cell F2 which can be dragged down and across to complete the table

=SUMPRODUCT(--($B$3:$B$9=$E2),--($C$3:$C$9=F$1))


and for the totals ( which doesn't need sumproduct as there is only one condition, a sumif would suffice)

=SUMIF($C$3:$C$9,F$1,$A$3:$A$9).

( chippys should work fine also, although would recommend you don't have the fred's etc hard coded in the formulas)