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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I hope that makes sense.
Sorry, no, it's far from clear.

Are you saying the only possible values in columns A, B and C are 10 or 0 or NA (or blank?) If so, then then summing values > 0 for column D will be the same as summing all values?

In column E, it sounds like you add the 10's only if all three values are 10?

ABCDE
11010103030
20100100
31010NA200
Sheet1
Cell Formulas
RangeFormula
D1:D3D1=SUM(A1:C1)
E1:E3E1=IF(D1=30,30,0)

A few more examples would help us understand what you're after.
 
Upvote 0
The table below hopefully gives more clarity as to exactly what formula I will need to get the results in cells I2, I3, I4 etc to sum cells A2:G2, A3:G3 and A4:G4 so that I can apply that formula to as many rows below. The cell values in A2:G2, A3:G3 and A4:G4 are based on A1 x 100 and so forth for each cell in each row unless there is a zero or a blank. The reason there may be a zero or a blank in any of those cells is because they are linked to another set of cells which if the response is "Y" then the return value is 1 and if "N" then zero and blank if left blank. So Row A4:G4 for example you will see that A1 has a 0, B4 =5, C4=0, D4=blank, E4=0, F4=5, G4=0. H4 sums just the values that are greater than 0 and I want I4 to sum all values as if there were no zeros but still not include the blank cells. You can see in H4 the total sum is 10 since the only values greater than zero in that row are B4 and F4. The sum I'm looking for with the correct formula will be 50 which includes A4 to equal 10 (10% x 100), B4 = 5, C4 to equal 5 (5% x100), D4 =0, E4 to equal 20 (20% x 100), F4 = 5, and G4 to equal 5 (5% x 100). H1 is the sum of H2:H4 and I1 will be the sum of I2:I4. Let me know if you need any more clarification. I'm probably over complicating this entire project but I'm hoping there is a formula that will give the desired outcome. Thanks.


ABCDEFGHI%
110%5%5%50%20%5%5%18024075%
21055502059595100%
310555005759579%
4050050105020%
 
Upvote 0
Thank you. What if the Results in C9 etc is a number 1 or 0 or blank? What would the formula be in A3:G5? Almost resolved.
 
Upvote 0
Also, I forgot to mention what if any of the cells in A3:G5 have NA? I noticed the formula counts that as a zero. I want that to be counted as a blank so the I3:I5 formula doesn't calculate the percentage equation. Thanks.
 
Upvote 0
Also, I forgot to mention what if any of the cells in A3:G5 have NA? I noticed the formula counts that as a zero. I want that to be counted as a blank so the I3:I5 formula doesn't calculate the percentage equation. Thanks.
Hi, just following up with this question. Thanks.
 
Upvote 0
Also, I forgot to mention what if any of the cells in A3:G5 have NA? I noticed the formula counts that as a zero. I want that to be counted as a blank so the I3:I5 formula doesn't calculate the percentage equation. Thanks.
I think that means you have #N/A (error value) rather than NA (text value) ...

If so, try:

ABCDEFGHIJ
1Scores
210%5%5%50%20%5%5%TotalPossible% achieved
310555020 59595100%
41055500 5759579%
5050 050105020%
610 #N/A50#N/A 0606592%
7
8Results
9111111
10111101
11010010
121#N/A1#N/A0
Sheet1
Cell Formulas
RangeFormula
A3:G6A3=IF(ISBLANK(C9),"",IF(C9="NA","NA",C9*A$2*100))
H3:H6H3=SUM(IFERROR(A3:G3,0))
I3:I6I3=100*SUM(ISNUMBER(A3:G3)*A$2:G$2)
J3:J6J3=IFERROR(H3/I3,"-")

But if you do mean "NA", then

ABCDEFGHIJ
1Scores
210%5%5%50%20%5%5%TotalPossible% achieved
310555020 59595100%
41055500 5759579%
5050 050105020%
610 NA50NA 0606592%
7
8Results
9111111
10111101
11010010
121NA1NA0
Sheet1
Cell Formulas
RangeFormula
A3:G6A3=IF(ISBLANK(C9),"",IF(C9="NA","NA",C9*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
Cell Formulas
RangeFormula
A3:G6A3=IF(ISBLANK(C9),"",IF(C9="NA","NA",C9*A$2*100))
H3:H6H3=SUM(A3:G3)
I3:I6I3=100*SUMIFS(A$2:G$2,A3:G3,"<>*")
J3:J6J3=IFERROR(H3/I3,"-")

for A3, i tried the formula above for "NA" but it does not account for if(C9="Y", $A2*100) or if(C9="N", "0"). Is there a way to include that in the formula? Thanks.
 
Upvote 0
What are all the possibilities for C9?

Are you saying we need to allow for C9 being "Y", "N", 1, 0, "NA" or blank?
 
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