• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Can it get shorter than either one of these two? SUMIF vs. SUMPRODUCT

F

fred

Member
I have 2 formulae one using sumif and the other one using sumproduct. Both get the same correct answer but they look a bit too long to me and i can't find a shorter way to express.


basically i need to find out the sales volumn by names and I have names on columns W, AA and AC. There are sales figures on columns Z, AB and AD that goes respectively to the name columns.


Here are my formulae. X1 is the name I'd type in to find out the combined sales figures


SUMIF($W$9:$W$136,$X$1,$Z$9:$Z$136)+SUMIF($AA$9:$AA$136,$X$1,$AB$9:$AB$136)+SUMIF($AC$9:$AC$136,$X$1,$AD$9:$AD$136)


VS.


SUMPRODUCT(($W$9:$W$136=$X$1)*($Z$9:$Z$136))+SUMPRODUCT(($AA$9:$AA$136=$X$1)*($AB$9:$AB$136))+SUMPRODUCT(($AC$9:$AC$136=$X$1)*($AD$9:$AD$136))


or do any of you have an even better idea? Any suggestion?
 
Hi, fred!


Would it be a silly answer if I say to create named ranges as:

NNN1: column W (proper range)

NNN2: column AA

NNN3: column AC

SSS1: column Z

SSS2: column AB

SSS3: column AD

and then replace them in the formulas?


They'd appear as:

=SUMIF(NNN1,$X$1,SSS1)+SUMIF(NNN2,$X$1,SSS2)+SUMIF(NNN3,$X$1,SSS3)


You can change the NNN/SSS format to any other short thing like N_1 or so...


Regards!
 
Thanks, SirJB7. Great idea. I thought about that, too. I was just weary if my boss would understand it. if (s)he doesn't understand it, (s)he would not like my formulae and ask me to change into something (s)he would.


and then there is the issue of expanding the name. this is just the first month of the quarter and by the end of the quarter the range would verly likely expand to over 1000 rows.


Yeah, i'd like to spend more time back on chandoo blog but i was just buried under 10 feet of work in the past couple of months. :)
 
Hi Fred,

You could give your data range a name, and use a formula such as the following:

=SUMPRODUCT(--(datarange=$X$1),(OFFSET(datarange,0,1)))


Here, "datarange" refers to the range of columns starting with the first "name" column, and ending with the last "name" column.

In your example, datarange would be $W$9:$AC$136


This is based on the assumption that the sales figures are in the column right after the names.


As you need to add more columns with names and sales figures, adjust the "datarange" to expand to the last "name" column.


Cheers,

Sajan.
 
@sthomas

Hi!

Am I missing something or columns X:Y are in the middle of 'datarange' for first part?

Regards!
 
Thanks, sthomas. But as you can see the first column of name and first column of data are not side by side. But may be in the future I can modify the report to utilize your formula.
 
Hi, all!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Can%20it%20get%20shorter%20than%20either%20one%20of%20these%20two_%20SUMIF%20vs.%20SUMPRODUCT%20%28for%20fred%20at%20chandoo.org%29.%20SUMPRODUCT%20%28for%20fred%20at%20chandoo.org%29.xlsx


The difference between first and second sheets is that the argument for search (yellow columns) might be repeated as value if either numeric or text with numeric contents. In first sheet (different values) the values of both formulas match and in second sheet (same values) the values of both formulas differ.


It's just a warning, as sthomas's idea is excellent!


Regards!
 
Hi, fred!


I missed a part your previous answer. I won't say that if s(he) doesn't understand such formulas it's (s)he and not the formulas who should be changed, but it crossed my mind...


About expanding the names at the data grows, you could always build dynamic ranges with this technique:

=OFFSET($W$9,0,0,COUNTA($W:$W)-XX,1)

for first range NNN1 related to range W9:W136 and with XX = number of non blank cells from W1:W8 -title rows- (and from W137:W1048576 -total or comment rows- if applicable)


Regards!
 
If you can shift the data from Column W to Column Y you can use:

=SUMPRODUCT(Y9:AD136 * Z9:AE136 * (MOD(COLUMN(Y9:AD9), 2) =1))
 
Hi Fred,

Reading your original post again, I realize that I missed the x, y cols being in there since the other columns were in a different pattern.


So, your options are to restructure your data and use the formula from Hui or me, or handle the first column as a special case, and use the formula for all of the other columns. In any case, you now have options to shorten your original formulas, and impress your manager!


Cheers,

Sajan.
 
Hello Fred,


Try SUMIF with OFFSET


=SUMPRODUCT(SUMIF(OFFSET(W9:W1000,,{0,4,6}),X1,OFFSET(Z9:Z1000,,{0,2,4})))


Note: OFFSET is a volatile function, if you have too may or complex calculation in the workbook, this may slow down the sheet performance.
 
Hi SirJB7!

Thank you! I thought it would be a nice change, and I am partial to the blue color!


Regards,

Sajan.
 
@sthomas

Hi!

Oh, your Highness, I beg to apologize for this humble subject who wasn't aware or your blue blood lineage.

Regards!
 
Hi SirJB7,

It was good to see you at the Olympics opening ceremony with the queen! Looking sharp, as always!


Regards,

Sajan.
 
@sthomas

Hi!

Ouchhh... you catch me, man. That's why I wasn't here since last Friday.

Regards!

PS: at least he didn't say "what a nice pink dress..."
 
You must log in or register to reply here.
Back
Top