SUMIF question

[RNiner]

Hi. I currently have a profit/loss spreadsheet where I use a SUMIF to calculate daily gains/losses.

=SUMIF('Daily Sheet'!B:B,B377,'Daily Sheet'!H:H)

Basically, on the 'Daily Sheet' page when a sale is made I write it in a row. Each sale gets its own row. One of the column's has the date. (Column B) Column H has the respective gain or loss for that sale/purchase. B377 in this case contains the date 18-Nov. (B360 to B389 has Nov 1 through Nov 30) So this cell will calculate all the gains and losses in column H for each sale/purchase entry I have for that day. Some days I have 30 rows, others only 2 or 3, so this works great.

My problem now is that my rows have gotten much more complicated and detailed, and now I'd like to be able to only sum column H:H if it meets TWO criteria. (I'd like to add in Column C that specifies the type of sale or purchase being made, which is R, T or S for my purposes.) So I'd like to somehow write a sumif that only adds up the sales for 18-Nov AND sale type R. Is this possible? If not, any other suggestions?

Thanks a bunch,
Rich

[Maistrye]

Sorry, I'm in one of those 'don't really feel like thinking' moods.

Here's a link:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You will have to read this carefully. Basically though, you can go like this:

=SUMPRODUCT(--(A1:A100="Cond 1"), --(B1:B100="Cond 2"), ... , H1:H100)

So this would sum column H, but only when "Cond 1" is in column A, and "Cond 2" is in column B, and so on.

If you have any questions, we'll be glad to answer.

Scott

[RNiner]

Thanks Scott! I'm a quick study, just need to be pointed to the book to read. If I get I'll ask, but looks like your link should be perfect. Gracias.

Rich

[Carim]

Hi,

Scott is right ... the solution is the sumproduct() function ...
Should you have difficulties applying it, best thing to do would be to upload a worksheet sample, for a tailor-made answer ...

HTH
Carim

[dominicb]

Good morning RNiner

if you struggle with the syntax then download my add-in via the link below. It has a utility that will generate SUMPRODUCT formulae for you - you just specify the criteria (Ultimate > Formulae > Multiple SumIf Generator).

HTH

DominicB

[RNiner]

Thanks for the tips guys! I got it working great, and now have more info then I can shake a stick at.

[RNiner]

I do have another question though.

Each month, I have about 40 formulas that use the same range #'s. For example, October used the row range 917 to 1259. November is using 1260 to 1451 (with more to come of course.)

At the beginning of each month, when I make my new monthly report section, I copy/paste a month over and then adjust the formulas for that month. Takes about 1/2 hour, and is quite the pain.

So, my question: Would it be possible to have the ranges put into two random cells, and then have all the formulas use that data for the range? For example, for November my formulas would be the same as October, they'd just reference the two cells next to November that say 1260 and 1451. No idea if this is possible or not... My thinking is no, but then I didn't think I could SUMIF two criteria either.

Thanks for any help!

[Bryan Hessey]

I do have another question though.

Each month, I have about 40 formulas that use the same range #'s. For example, October used the row range 917 to 1259. November is using 1260 to 1451 (with more to come of course.)

At the beginning of each month, when I make my new monthly report section, I copy/paste a month over and then adjust the formulas for that month. Takes about 1/2 hour, and is quite the pain.

So, my question: Would it be possible to have the ranges put into two random cells, and then have all the formulas use that data for the range? For example, for November my formulas would be the same as October, they'd just reference the two cells next to November that say 1260 and 1451. No idea if this is possible or not... My thinking is no, but then I didn't think I could SUMIF two criteria either.

Thanks for any help!

Hi,

did you mean asin

=SUM(INDIRECT("A"&C1&":A"&D1))

where C1 was 1260 and D1 was 1451 ?


note, you can Formula-Fill that where C2 = 1452 and D2 = 1500 etc

hth
---

[dominicb]

Hi RNiner

Two ways to do this. You could do it your way, which would involved the use of a macro - it's not difficult, just a bit of a fudged way of doing things -you need a trigger to set your macro off (manual or by events), and you would need to code each formula seperately. A neater solution would be to use named ranges a write the formulae to refer to the predefined names. An example workbook is attached showing both methods.

HTH

DominicB

[RNiner]

Thanks for the replies!

Bryan, that is indeed what I'd like to do. Pardon the ignorance, but what is formula-fill? I did an Excel Help search that came up dry.

Dominic, your second solution looks promising. How did you get that range named that way?

Thanks guys!

[dominicb]

Hi RNiner

If you want to go down the named range route, then you can set the range by highlighting a range in the spreadsheet and selecting Insert > Name > Define. Type a name in the box at the top, your selected range should be in the Refers to box. Just click Add and then Close.

HTH

DominicB

[RNiner]

Perfect Dominic! I can adjust the range of each month's formulas at the end of the month when I have an exact finishing row #. (I have 6 key ranges each month) Another huge time saver. You guys are great.

Question: Is there a limit to how many Names I can have? With 6 each month I could run into trouble if there is a capped limit.
Final Question: Is there a way to grab a bunch of cells that have formulas and such with words in it, and replace all instances of one word with another? So if I have the formula:

=SUMIF(Nov06C,AO359,Nov06M)

and other variations using Nov06D, Nov06J, etc. in about 50 different cells, could I select all cells, and replace Nov06 with say, Jan07?

Thanks a bunch guys!

[dominicb]

Hi RNiner

Is there a limit to how many Names I can have?

The amount of named ranges you can use is limited only by available memory, so as long as your computer isn't so old you have to wind it up in a morning you should be OK.

could I select all cells, and replace Nov06 with say, Jan07?

Certainly, just use the Find and Replace faclity under the Edit menu. But that's not all. Named ranges don't have to be contiguous (adjacent). There's no reason why you couldn't select all these cells and set them all up as one named range. Then call the name either from the named range drop down (top left above main working area) or via the Edit > Go To screen to select them all at once. Just to make your life that little bit easier...

HTH

DominicB

[Bryan Hessey]

Thanks for the replies!

Bryan, that is indeed what I'd like to do. Pardon the ignorance, but what is formula-fill? I did an Excel Help search that came up dry.

Thanks guys!

RNiner,

another option as opposed to setting a named range for each set of rows.

In column (say) B, the list of periods (month names for your purpose)
In column (say) C, cell C2 the formula =D1+1 will give you the next row after the previous range,
in column (say) D, cell D1 the number of the previous range, and D2 the number that is the end of the range for the period
in column (say) E, cell E2 the formula as shown

=SUM(INDIRECT("A"&C1&":A"&D1))

=SUM(INDIRECT("'Daily Sales'!A"&C1&":A"&D1))


which takes the number in the C column and in the D column to make a Sum formula for the required number of rows, by setting

=SUM( "'Daily Sales'!A" 1260 ":A" 1451 )

The 'easy' part is that you can simply record the new row in column D (or perhaps this can be formula-deduced) and Formula Fill column C and E to your needs without the need to modify a formula to use a new range.

Fornula Fill is described at
http://www.mvps.org/dmcritchie/excel/fillhand.htm
and is something you wil need to learn for Excel.


note, this suggestion is just to show another option, whether this method or the Named Range method is more suitable to your purpose is something only you can decide, perhaps a SumProduct is a third option you should explore.
hth
---