Okay So i'm having a brain freeze at the moment and need some help with a formula in excel. I have tabs in a worksheet and have two conditions I want met on the totals page. Basically I want to check if a partner is equal to let's say NBA and the ESP is equal to GPM to add the value of sales colum to my totals cell on the second workbook. I will have this function for a total of about 10 different combination and have them add separately into the totals page. I have this right now which it checks the conditions properly but it doesn't add the values into the cell, it just shows 0. =IF(AND('Offer Performance'!F:F="NBA",'Offer Performance'!B:B="GPM"), +'Offer Performance'!H:H,0) So to recap first condition it checks to see if colum F has "NBA" and Column B has "GPM" and i want to take the value in Colum H from that sheet and add it to a running total. Thanks in advance.
Hi Stack, IF(Statement) will only return two values, true (1) or false (0) I think the expression shall appear something like this: =IF(AND('Offer Performance'!F:F="NBA",'Offer Performance'!B:B="GPM")) * 'Offer Performance'!H:H,0 Hope this will work.
It works when I do it. But, understand it only shows the amount that is on the same row as the IF formula. It doesn't sum everything in all rows. I can't tell from your description if you want one formula to sum everything for you or just to show the value when a condition is met. If the former, SUMIF might be more appropriate. If the latter, you need to have the formula on the same row or else use cell references instead of column references in the formula.
I will try this out. Basically i want to check if those 2 conditions are true. If they are true to add the number in the specified column to a running total. If it does not meet those two conditions then I don't want it to add it.
I will try the SUMIF...yeah I want it to take any value from that column if the conditions are met. I don't want it to only take the value of that same row.
I'd paste the following into your total page and then paste it down for all of the rows in your master data. =IF(AND('Offer Performance'!F1,'Offer Performance'!B1),'Offer Performance'!H1,"") Then sum up the the individual returns to get your number. That's a quick, dirty, and easy way to get the results. I'd normally use SUMIF, but with two criteria, it doesn't seem to work quickly.
I was trying the SUMIF but couldn't get it to work right. I was trying to keep from having a separate page that had all the individual data and then sum it up because the master sheet will have a ton of entries.
Try... =Sum(IF(AND('Offer Performance'!F:F="NBA",'Offer Performance'!B:B="GPM"), 'Offer Performance'!H:H,0))
That just added up every thing in the H colum and didn't use the criteria. I'm looking up SUMIF with multiple criteria to see what i can find... thanks for all your help guys.
not entirely clear what you are trying to do, but you can always try the sumif formula for one column and then just a sum(if( formula with an array for both I.e. A B C D NBA Other 1 NBA Other GPM 3 GPM NBA GPM 2 NBA Other 5 Other GPM 8 If I wanted to sum up the numbers in column C only if column A = NBA, then I'd use: =+SUMIF(A1:A5,D1,C1:C5) If I wanted to sum up the numbers in column C only if column B = GPM, then I'd use: =+SUMIF(B1:B5,D2,C1:C5) If I wanted to sum up the numbers in column C only if column A = NBA AND column B = GPM, then I'd use: =+SUM(IF(A1:A5=D1,IF(B1:B5=D2,C1:C5,0),0)) On the last one, though, note it is an array formula, so you have to use Ctrl+Shift+Enter when entering for it to work.
Okay so this link helped me out. Basically I wanted to do a SUM using two criteria. Thanks for everyone help. http://www.officearticles.com/excel/sumif_using_multiple_criteria_in_microsoft_excel.htm The thing is you can't just use a full colum you have to give it a range in both criteria columns. i found a lot of other fixes but this one was the easiest one. Sum of units sold where vehicle type is Cars and Color is Black. The SUMIF function doesn’t work for multiple criteria in different fields. However, you can use an array formula (sometimes referred to as a CSE formula), which requires you to press Ctrl+Shift+Enter after typing it, instead of just Enter. =SUM((A2:A10=”Cars”)*(B2:B10=”Black”)*C2:C10)
That's not going to give you what you want. Because you could have a black bike or a black plane, which would return a positive count based on the color. You could also have red cars and blue cars, and you'd get positive counts because of the vehicle type. You could slap a pivot table on it. Filter by vehicle type on the left and color on the top and then sum up your totals. Multiple criteria frequently require a staging area to sum things up first, then you can pretty it up for the final page. I usually range value all of the secondary rows so there aren't as many formulas. Keep the top row because you might want to recalculate and can paste it down as needed.
I stand corrected. That did work correctly. I'll have to add that one to my bag of tricks. Nice find, <B>Stack24</B>.
yeah the first thing i read was to do some pivot tables but wanted a simple fix. This actually works but the only thing that i wish it would do is actually let you put in a full column like B:B instead of a range of the column for it to work. That would make life easier because the more data I have to enter I have to go back and change the range as well.
Just extend the range down to row 25000 or so. Or if you put more data in, make sure you insert the rows, which will automagically update your formula. btw...pivot tables rock. I deal with a lot of financial transactions and need to set up data cubes and sum up data. Pivot tables are powerful tools.
Your problem may be solved, but I have more solutions. For the sumif with 2 criteria, you'd have to make one more column to concatenate the other two. Then you sumif looking for "NBAGPM." You can also use DSUM, which is a database functionality. You can specify all the criteria you want. The only drawback is that it takes a couple of cells to set up your criteria and it doesn't always look pretty (though you can hide that stuff).
I tried to extend the rage down to a higher number but the value doesn't work if three is nothing in there. I guess I could always just put a place holder in those cells until I change them when I am doing my entry. I have to learn more about these pivot tables.
I do these with SUMPRODUCT (which is mentioned in one of the links above), but you can also do this sort of stuff in 2007 with COUNTIFS and SUMIFS. As a heavy excel user, I am a big fan of Excel 2007.
