Excel Question!

Nalz

Hi folks,

Any experts out there ? Need some help with excel if anyone out there can give me a dig out!

I have a table that may contain the strings "major" or "critical" in column A.

I need to find the row where the string is (could be any row i.e. A14, A16 or A22).

Once I find the row its on, I need to reference that row for Columns B, C and D and add those together.

so if "Major" was at row 16, I need to add the contents of B16, C16 and D16.

thanks,
Trilla

homer911

=if(A16="Major",sum(B16:D16),0)

Then just copy this formula to the other rows in the sheet alongside your data

Al Capwned

Put this in top row.

=IF(A1="Major",SUM(B1:D1),0)

What this will do is add up B1, C1 and D1, but only where A1 contains the text "Major". If it has anything other than "Major", it'll insert a zero.


EDIT - what he said above!!

What needs to happen if "critical" is in column A?

Nalz

Thanks guys

Say I want to print out in one cell the values of B at Row x where x = "major" or "critical" or "blocker"


I thought if I could do something painful like this in the top bar it would work:

=if(A6="Major", "Critical", "Blocker", B6), if(A7="Major", "Critical", "Blocker", B7), if(A8="Major", "Critical", "Blocker", B8), if(A9="Major", "Critical", "Blocker", B9), if(A10="Major", "Critical", "Blocker", B10), .......etc etc

Any ideas how I could do this?

Al Capwned

The text in the formula bar only applies to the currently selected cell.
So if you get your formula right in Row 1, then you can click the bottom right hand corner of that cell, and drag down to include as many rows as you require.
Excel automatically changes B1 to B2 to B3 etc as you drag down. (Unless you specifically tell it not to, which is another lesson! )

_____

So you have either Major, or Critical or blocker in Column A - and more information in columns B,C,D beside that.

Is "What do you want in column E?" the question so?

Al Capwned

Hopefully this should work for you.

Put this formula in E1

=IF(A4="Major",B4+C4+D4,IF(A4="Critical",B4*C4*D4,IF(A4="Blocker",B4*C4/D4,0)))

This will do one of 4 things depending on what is in A1

1. "Major" in A1 - Result is E1 contains B4+C4+D4
2. "Critical" in A1 - Result is E1 contains B4*C4*D4
3. "Blocker" in A1 - Result is E1 contains B4*C4/D4

(You can change to what you require.)

Then grab the little square at the bottom right of E1. (Shown as B2 in pic) And drag it down to auto fill all required rows in Column E.

Nalz

Thanks again

What I require though guys is to

(a) Find out where in column A "major" exists
(b) Once I find that row - reference the values beside it in columns B, C and D
(c) Place those values in another table\cell

then do the same for "critical" and "blocker"


ie

If major is in A12, I then want to take the values beside that in row 12 and reference B12, C12 and D12. I then want to place those values in another table. I don't think dragging the square down and using multiple result\destination cells will help me.

Hope this makes sense and cheers for the help so far!

Al Capwned

Trilla wrote: »

Thanks again

What I require though guys is to

(a) Find out where in column A "major" exists
(b) Once I find that row - reference the values beside it in columns B, C and D
(c) Place those values in another table\cell

then do the same for "critical" and "blocker"


ie

If major is in A12, I then want to take the values beside that in row 12 and reference B12, C12 and D12. I then want to place those values in another table. I don't think dragging the square down and using multiple result\destination cells will help me.

Hope this makes sense and cheers for the help so far!

Is it possible to sort all the data in the sheet by column A and group all the major's together?

If not, i think I know what you mean and you need to use the vlookup() function.

homer911

Trilla wrote: »

Thanks again

What I require though guys is to

(a) Find out where in column A "major" exists
(b) Once I find that row - reference the values beside it in columns B, C and D
(c) Place those values in another table\cell

then do the same for "critical" and "blocker"


ie

If major is in A12, I then want to take the values beside that in row 12 and reference B12, C12 and D12. I then want to place those values in another table. I don't think dragging the square down and using multiple result\destination cells will help me.

Hope this makes sense and cheers for the help so far!

Al provided you with a nested if statement - place the formula in column E. If you want in a separate column, then use the original solution proposed and modify the formula for say solumns F and G, although now it sounds like you are in pivot table territory - perhaps you could upload a sample sheet?