-
Super Member
Anyone here good with Excel? I have a formula question
I'm pretty crummy at Excel, so I probably can't explain this well enough for anyone to understand, but I'll try.
Right now I have columns with plain numbers in them, the only formula's in the last cell if each column and it add's up all the cells in the same column that are above up. Here's what I want to do
let's say right now B2's 60 and B3's 60 & B4-10 are empty, B11 add's them up for 120. Was simple enough for even me to figure out. What I'm trying for, A2 & A3 have 60 in them. And I change B2 & B3 to the letter X. And B11 still calculates, but I don't know the formula at all to have it pull the numbers from the A column. Right now I have 12 columns, and they all have numbers. It looks pretty cluttered imho. Here's a sample screen shot, not the spreadsheet I'm working on, I made it to maybe help make my gibberish make sense to someone.
Row 11 are simple =SUM(X2:X10)
I want row 23 to add up like row 11, but it's going to have to pull the numbers from column a. Now, I could do this easy if I needed everything from column A. But like row 11 does, I want it to only pull numbers from cells in rows 14-20 that have an X in them. There's a formula to do this, but I went to California public school and my high school topped out at Pre Algebra, so I'm up the creek here if left to my own devices lol. In the screen shot I manually put the numbers in row 23 to show what I want it to do for me.
-
Re: Anyone here good with Excel? I have a formula question
I think the formula you are looking for is "SUMIFS". In cell B23 in the above example (where you have "125"), you would enter the following:
=SUMIFS($A$14:$A$20,B14:B20,"=X")
This formula adds up the numbers in column A ($A$14:$A$20) where the corresponding cell in column B (B14:B20) is equal to the value 'X' ("=X"). I used the dollar signs to tell Excel which cell references are absolute and relative so, if you copied this formula to column C, it would still work as intended.
However, this gives you a result of 210 (60+10+20+120), not 125.
Let me know if this doesn't help.
-
Post Thanks / Like - 1 Thanks, 1 Likes, 0 Dislikes
-
Super Member
Re: Anyone here good with Excel? I have a formula question
YEEEEES! THANK YOU!!! Looking at your formula I understand it enough to understand it. But left to my own devices I would have never in a million years came up with that.
Bookmarks