# That Sum Problem!

#### Jen in Florida

##### New Member
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

### Excel Facts

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
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.

Regards,

Replies
1
Views
308
Replies
3
Views
454
Replies
0
Views
274
Replies
7
Views
147
Replies
0
Views
229

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.

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.

### Which adblocker are you using?

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

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