if formula maybe?

[tmk0427]

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

 

[StephenCrump]

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?

	A	B	C	D	E
1	10	10	10	30	30
2	0	10	0	10	0
3	10	10	NA	20	0
Sheet1

Cell Formulas
Range	Formula
D1:D3	D1	=SUM(A1:C1)
E1:E3	E1	=IF(D1=30,30,0)

A few more examples would help us understand what you're after.

 

[tmk0427]

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.

	A	B	C	D	E	F	G	H	I	%
1	10%	5%	5%	50%	20%	5%	5%	180	240	75%
2	10	5	5	50	20		5	95	95	100%
3	10	5	5	50	0		5	75	95	79%
4	0	5	0		0	5	0	10	50	20%

 

[StephenCrump]

Ok, thanks. Try this:

	A	B	C	D	E	F	G	H	I	J
1	Scores
2	10%	5%	5%	50%	20%	5%	5%	Total	Possible	% achieved
3	10	5	5	50	20		5	95	95	100%
4	10	5	5	50	0		5	75	95	79%
5	0	5	0		0	5	0	10	50	20%
6
7
8			Results
9			Y	Y	Y	Y	Y		Y
10			Y	Y	Y	Y	N		Y
11			N	Y	N		N	Y	N
12
Sheet1

Cell Formulas
Range	Formula
A3:G5	A3	=IF(ISBLANK(C9),"",(C9="y")*A$2*100)
H3:H5	H3	=SUM(A3:G3)
I3:I5	I3	=100*SUMIFS(A$2:G$2,A3:G3,"<>*")
J3:J5	J3	=H3/I3

 

[tmk0427]

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.

 

[tmk0427]

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.

 

[tmk0427]

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.