Modify formula to automate(scroll) calculation

**alcharbonneau** · 04-01-2013, 08:18 PM

Hi

I am comparing two years of data for a large number of organizations. I have modified a formula which I obtained from one of the skilled participants on the Excel Help Forum to sum the values associated with a given word: =SUMPRODUCT(--($L$2:$L$136526="MACHINERY"),(($E$2:$E$136526 ="00100")+($E$2:$E$136526 ="00200")), $F$2:$F$136526). In the attached worksheet, the word MACHINERY HAS TWO VALUES THAT TOTAL TO $2,949,107. With the current formula, I have to insert the text into the formula in order to get the total for one category then I move to the next category. This approach appears to be working for a limited number of organizations. I am looking at analyzing a much larger number of companies and I would like to know if this formula can be automated so that it can be scrolled down and yield the values for each item in text.

Thank you for reading and/or considering this request.

Al Charbonneau

**benishiryo** · 04-01-2013, 08:30 PM

hi Al, looks like you're always stuck with SUMPRODUCT eh?

the subtotal you have in column M is a little inconsistent. should the subtotal be on the 1st time the text appears or the last? for Machinery, you placed it first. for Activities, you placed it last. here's how you can do either:
=IF(L2<>L1,SUMPRODUCT(--($L$2:$L$136526=L2),(($E$2:$E$136526 ="00100")+($E$2:$E$136526 ="00200")), $F$2:$F$136526),"")
=IF(L2<>L3,SUMPRODUCT(--($L$2:$L$136526=L2),(($E$2:$E$136526 ="00100")+($E$2:$E$136526 ="00200")), $F$2:$F$136526),"")

**alcharbonneau** · 04-01-2013, 10:09 PM

Good Morning (it's mid-morning in Singapore is it not") Great to hear from you.

You equation, not surprisingly, worked perfectly. One more question (for this evening) if I may: is there a way to move or copy and paste only the text in column L that has a value in column M so that you end up with only the name of each category and its corresponding total value?

Thanks again for one of your elegant solutions.

Al

**benishiryo** · 04-01-2013, 10:19 PM

ahh, you're quite familiar with our time here eh? it's 10:15 am here right now.

glad that worked for you. without using formulas, do an Autofilter & unselect blanks in column M. you can then copy both the columns & paste elsewhere. since you're managing 100,000 over rows, i shall not introduce you to a resource heavy way of finding unique values in column L. hope that is sufficient for your need