# Thread: question about SUMPRODUCT (2002 SP3)

1. ## question about SUMPRODUCT (2002 SP3)

Hi,

I've just tried using SUMPRODUCT for the first time and was wondering how flexible it is. I have a large extract that I pull each month and used SUMPRODUCT to calculate Costs and Days for various divisions. I was wondering if I could add a layer to the divisions to get departments within the divisions.

eg ColA =Division ColB=Dept ColC=Cost and Col4= Days

at the moment I have

Division Cost Days
BS 450. 45
NS 880 64
SS 777 59

Could I get

Division Dept Cost Days
BS A 200. 15
BS B 250. 30
NS C 480 34
NS D 400 30
SS E 377 29
SS G 400 30

Or would I have to settle for

Dept Cost Days
A 200. 15
B 250. 30
C 480 34
D 400 30
E 377 29
G 400 30

capri

2. ## Re: question about SUMPRODUCT (2002 SP3)

Hi capri,

SUMPRODUCT is quite flexible, you can add up to 30 arguments. But wouldn't a pivot table be easier? You wouldn't have to create formulas.
If you'd like to pursue formulas with SUMPRODUCT, please provide some more information about the formulas you're using now, or post a small sample workbook (with dummy data).

3. ## Re: question about SUMPRODUCT (2002 SP3)

Hans,
Each month I pull a data extract which has year to date information (our year run July to June). Sometime people want to see the month to month information and other times they just want year to date.
Pivot tables are easy, and that is what I do now, however I thought this method would save time each month, if I just copied and posted the sumproduct tables (replacing the previous final row of data with the current final row). It would save re-doing pivot tables each month. It might also save on file size as pivot tables tend to bloat my files. My monthly extracts run from 5,000 to 60,000 rows of data depending on the month.

Attached is a sample of what I have and what I would like. I just need to know how to construct the formula in the shaded green cells.

capri

4. ## Re: question about SUMPRODUCT (2002 SP3)

You can enter

=SUMPRODUCT((MONTH(\$E\$2:\$E\$344)=7)*(\$C\$2:\$C\$344=\$O 23)*(\$B\$2:\$B\$344=\$P23)*\$D\$2:\$D\$344)

in Q23, and

=SUMPRODUCT((MONTH(\$E\$2:\$E\$344)=7)*(\$C\$2:\$C\$344=\$O 23)*(\$B\$2:\$B\$344=\$P23)*\$F\$2:\$F\$344)

in R23, then fill down. See attached version.

5. ## Re: question about SUMPRODUCT (2002 SP3)

Thanks Hans,

That's exactly what I was looking for. Once I set it up, I'll just copy and paste each month, use find and replace to increase the size of the data extract, and I'll have the numbers I need to use, without having to pivot the data, and hopefully my file sizes won't be as large. I regularly lose my work having too many large files with pivot tables open at once, as my PC struggles to handle the data. I can see a lot of potential with this feature of eliminating lots of data I usually pivot. My IT area has been no help with my problems, so anything I can do to save file size makes my life easier.

capri

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•