Repeat formula within cell [Solved/Closed]

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

Thanks in advance to anyone who answers this

See more 

5 replies

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Feb 17, 2011 at 02:21 PM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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.