That Sum Problem!

[Jen in Florida]

N/A	N/A	09HETJON		09HETJON	09HETJON
09ICEJAS	09ICEJAS	N/A		09ICEJAS	09ICEJAS
03METDEN	03METDEN	03METDEN		03METDEN	N/A
$100	$100	$50	$0	$100	$300

In the above spreadsheet, I am trying to show who gets how much in bonuses. (By the way - I downloaded the HTML Maker Utility to display the spreadsheet, but I don't know how to install it - sorry about the appearance of the above spreadsheet.) There are 6 columns for 6 days in the work week. Sunday we are off. There was no bonus given for Thursday.

If the second row has an ID, then only he would get the bonus. If the second row has "N/A", then the next row gets the bonus. If that row has "N/A", then the 3rd row gets the bonus. If the bonus amount is 0, then everyone gets that bonus.

By this one week, 09HETJON would get $450, 09ICEJAS would get $200 and 03METDEN would get none. I figured I needed 2 formulas: An If function to look for the "N/A"'s in the group and a SUMIF function to add up what the IF function found. I can't seem to make it work together. Where am I going wrong?

I have a ton of weeks and 20 stores to do this for. Could you please help me?

Thank you!
Jen

 

[Barrie Davidson]

How about something like this? In G1, put this formula:

=(A1<>"N/A")*A$4

where A4 is the total bonus amount. Copy this formula across to column L. Then put this formula in G2:

=IF(SUM(G$1:G1)=A$4,0,(A2<>"N/A")*A$4)

and copy across to column L.

You will then have the bonus amounts in each row.

Does this help you out?

Regards,