if formula maybe?

tmk0427

Board Regular
Joined
Dec 14, 2005
Messages
82
Office Version
  1. 365
Hi, I'm trying to figure out the best formula to sum each row of cells in D1 and E1 as outlined by the following:

A1 B1 C1 D1 E1
10 10 10 30 30
A2 B2 C2 D2 E2
0 10 0 10 30
A3 B3 C3 D3 E4
10 10 NA 20 20

If any of the cells A1:C1, A2:C2, A3:C3 have max value of 10 or 0 or NA then D1, D2, D3 should sum the values that are greater than zero (the formula I think will work for the D column cells is =sumif(A1:A3,">=0"). However, in E1, E2, E3 I would like a formula that sums the values in the row of cells A1:C1, A2:C2, A3:C3 to include 10 even if there is a 0 value but not include 10 if there is a NA or blank value in of those row cells.

I hope that makes sense.

Thanks.
TK
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Thanks, In that case, try:

ABCDEFGHIJ
1Scores
210%5%5%50%20%5%5%TotalPossible% achieved
310555020 59595100%
4105550NA 57575100%
5050 050105020%
610 NA50NA 0606592%
7
8Results
9Y11111
101Y11NA1
11N1001N
121NAYNA0
Sheet1
Cell Formulas
RangeFormula
A3:G6A3=IF(ISBLANK(C9),"",IF(C9="NA","NA",OR(C9="Y",C9=1)*A$2*100))
H3:H6H3=SUM(A3:G3)
I3:I6I3=100*SUMIFS(A$2:G$2,A3:G3,"<>*")
J3:J6J3=IFERROR(H3/I3,"-")
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

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
Back
Top