Repeat formula within cell
Solved/Closed
Related:
- Repeat formula within cell
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Date formula in excel dd/mm/yyyy - Guide
- Number to words in excel formula - Guide
- Excel grade formula - Guide
- Excel formula to subtract one cell from another - Excel Forum
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 17, 2011 at 02:21 PM
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 17, 2011 at 06:32 PM
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