 # Repeat formula within cell [Solved/Closed]

- - Latest reply:  MoonMemo - Feb 17, 2011 at 07:10 PM
Hello,

im a newb with excel and I need to know how would you make this formula repeat within the same cell..

=IF((AND(E9="DOA",S9="Hardware")),1,(IF((OR(E9="DOA",S9="NTF")),0,0)))

but in this fashion

=IF((AND(E9="DOA",S9="Hardware")),1,(IF((OR(E9="DOA",S9="NTF")),0,0)))
+IF((AND(E10="DOA",S10="Hardware")),1,(IF((OR(E10="DOA",S10="NTF")),0,0)))
+IF((AND(E11="DOA",S11="Hardware")),1,(IF((OR(E11="DOA",S11="NTF")),0,0)))
+IF((AND(E12="DOA",S12="Hardware")),1,(IF((OR(E12="DOA",S12="NTF")),0,0)))

See more

## 2 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753
0
Thank you
Could you explain bit more ?
oops I guess I have to reply here...

I guess I need that specific formula

=IF((AND(A1="DOA",B1="Hardware")),1,(IF((OR(A1="DOA",B1="NTF")),0,0)))

however its supposed to add up a list of values all the way down the spread sheet
and the only way I was able to figure it out was to manually do

=IF((AND(A1="DOA",B1="Hardware")),1,(IF((OR(A1="DOA",B1="NTF")),0,0))) + =IF((AND(A2="DOA",B2="Hardware")),1,(IF((OR(A2="DOA",B2="NTF")),0,0)))
plus row 3 (A3, B3) then 4 then 5 etc. however there are over 500 rows so.. is there like a formula that will do this over a range of cells?

What im trying to do is only count one value in a column if another value in the same row is correct but I need to do this over and over and then add them all up

A | B |
--------
1| X | Y |
2| X | Z |
3| X | Y |
4| N | Z |
5| X | Z |
6| N | Z |

For example, I only want to know how many Zs there are in column B IF column A is X
and I figured out that formula given above but that only works for each individual row.
i would have to individually add up each row and thats pretty time consuming... is there an easier way?

Thanks
0
Thank you
I guess I need that specific formula

=IF((AND(A1="DOA",B1="Hardware")),1,(IF((OR(A1="DOA",B1="NTF")),0,0)))

however its supposed to add up a list of values all the way down the spread sheet
and the only way I was able to figure it out was to manually do

=IF((AND(A1="DOA",B1="Hardware")),1,(IF((OR(A1="DOA",B1="NTF")),0,0))) + =IF((AND(A2="DOA",B2="Hardware")),1,(IF((OR(A2="DOA",B2="NTF")),0,0)))
plus row 3 (A3, B3) then 4 then 5 etc. however there are over 500 rows so.. is there like a formula that will do this over a range of cells?

What im trying to do is only count one value in a column if another value in the same row is correct but I need to do this over and over and then add them all up

A | B |
--------
1| X | Y |
2| X | Z |
3| X | Y |
4| N | Z |
5| X | Z |
6| N | Z |

For example, I only want to know how many Zs there are in column B IF column A is X
and I figured out that formula given above but that only works for each individual row.
i would have to individually add up each row and thats pretty time consuming... is there an easier way?

Thanks
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753 -
try some thing like this

=SUMPRODUCT((B1:B6="X") *(C1:C6="Z") * 1)
Oh wow
Thank you so much, this worked perfectly.
Recommended

DON'T MISS