# Repeat formula within cell

Solved/Closed
Related:

- How to repeat same cell in excel formula
- Excel formula if cell contains text then return value in another cell ✓ - Forum - Excel
- Excel formula if cell contains date ✓ - Forum - Office Software
- Excel conditional formatting formula if cell contains text ✓ - Forum - Excel
- Excel formula if cell contains multiple text ✓ - Forum - Excel
- Excel formula if cell contains date then return date in another cell ✓ - Forum - Excel

## 2 replies

rizvisa1

Feb 17, 2011 at 02:21 PM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

Feb 17, 2011 at 02:21 PM

Could you explain bit more ?

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

=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

Feb 17, 2011 at 06:32 PM

- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022

Feb 17, 2011 at 06:32 PM

try some thing like this

=SUMPRODUCT((B1:B6="X") *(C1:C6="Z") * 1)

=SUMPRODUCT((B1:B6="X") *(C1:C6="Z") * 1)

Feb 17, 2011 at 04:52 PM

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