# Repeat formula within cell

Solved/Closed
MoonMemo - Feb 17, 2011 at 01:41 PM
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)))

## 2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 17, 2011 at 02:21 PM
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
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
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Feb 17, 2011 at 06:32 PM
try some thing like this

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