That Sum Problem!

Jen in Florida

New Member
Joined
Dec 26, 2003
Messages
16
Code:
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 :unsure:
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
How about something like this? In G1, put this formula:
Code:
=(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:
Code:
=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,
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,096
Messages
5,768,068
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top