I have 3 columns of data - “J” column is either purchase or sale “AA” column is name of bank “AF” column is amt of contract Say I want to total the amount of purchases for Goldman, what formula would I use? I tried a sumif formula but I can’t differentiate btw “sale” and “purchase” Also what formula would I use to count the total # of purchases form a particular bank? I tried this to no avail… =COUNT(IF((J13:J1590=“PURCH.”)*(AA13:AA1590=“Goldman”),AF13:AF1590)) Thanks!
Did you try the sum(if(…=X,if(…=Y,AF13:AF1590)))?
Use conditional summing… {=sum(if(J1:J1000=“sale”,if(AA1:AA1000=“goldman”,AF1:AF1000))} or a pivot table
ChadD Thanks for the tip - Still returning #Value error… any thoughts? =SUM(IF(J13:J1590=“PURCH.”,IF(AA13:AA1590=“Goldman”,AF13:AF1590)))
you have to hit CTRL + SHFT + ENTER when you are at the end of the formula to have it work properly
I think this works… ={SUM((J13:J1590=“Purch”)*(AA13:AA1590=“Goldman”)*(AF13:AF1590))} (i.e. no need for if stmt)
fxguy1234 Wrote: ------------------------------------------------------- > ChadD Thanks for the tip - Still returning #Value > error… > > any thoughts? > > =SUM(IF(J13:J1590=“PURCH.”,IF(AA13:AA1590="Goldman > ",AF13:AF1590))) These equations can be a bitch. You need to make sure the text in your data and your formula is exactly the same. Probably best to copy/paste reference cells from your data to base your IF statement on. And hit Ctrl+Shift+Enter. A pivot table would be much better for this situation.
Crude way to do it is to sort the data by bank and transaction type and then use the subtotals feature. BTW, the software package STATA is great for this type of thing.
=if(J1=“Purchase”,if(AA1=“Goldman”,AF1,0),0) If you use that formula in a column you can grab all the goldman purchases and just add a simple sum formula at teh bottom row. Or you can do: =if(AA1=“Goldman”,sumif(J1:J9999,"=Purchase",AF1:AF9999),0) That should work for Goldman Purchases. Then put it in another cell for Goldman Sales, and so on.
Oh wait you just want to know the # of purchases and not the amount…sorry. You could do this: In 1 column: =if(AND(AA1=“Goldman”,J1=“Purchase”),1,0) =countif(range,"=1") It’s hard to do without seeing the data set.
=COUNT(IF(AA1:AA104=“goldman”,IF(J1:J104=“purch”,AF1:AF104))) =SUM(IF(AA1:AA104=“goldman”,IF(J1:J104=“purch”,AF1:AF104,0))) You need to hit ctrl+shft+enter and that should work
I love how there are sooo many different ways it can be done. Excel is awesome except for its pitfalls, like nesting limits and column limits, which I think a lot of were handled with 2007 edition. Too bad we are still on 2003…