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)))

Thanks in advance to anyone who answers this

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 ?
0
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
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
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)
0
Oh wow
Thank you so much, this worked perfectly.
0