Help with SUMPRODUCT please : )

[Sierra]

Here is what I'm dealing with:

Summary sheet:
A B C
1 222220 3330 (Total of data from column L on other worksheet)
3333
33353
2 222220 3330
3330
33353
3 222229 3330
3333
33353

Dump sheet(different worksheet, same workbook)
Cannot have colomn headers/titles

A B C ............ L
1 222220 ..... 3333 ..... 596.35
2 222220 ..... 3330 ..... 254.98
3 222220 ..... 3330 ..... 1547.36
4 222229 ..... 3330 ..... 259.36
5 222229 ..... 33353 ..... 12.36


I need to the sum of the data in column L if the row contains a particular
number in column A and a particular number in column C. For example, I need
the total of expenses in account 222220 AND within department 3330 (so that
would be 254.98 and 1547.36). The data goes on for a few thousand lines, so a
formula to add the data automatically would be great!

Thanks to the help I've already received on this site, I have tried:

=SUMPRODUCT(--('FAST Dump'!$A$1:$A$2000=B6), --('FAST
Dump'!$C$1:$C$2000=C10), 'FAST Dump'!$L$1:$L$2000)

Where 'FAST Dump' is the data sheet, B6 is my first criteria and C10 is my
second criteria. All I get is '0'.

The formula evaluation apparently works out the fist part okay (--('FAST
Dump'!$A$1:$A$2000=B6), but the problem comes with the second part --('FAST
Dump'!$C$1:$C$2000=C10), where all results are returned FALSE and there are
definately multiple 'C10''s in the data sheet area selected. It does this
whether I hit 'ctrl, shift, enter' for the array or not, including the
dollar signs or not, selecting the data range or typing it in, and selecting
the cell with the criteria (B7 and C10) or simply typing in the criteria.

I'm up to 12 hours now spent on trying to work this out, and it's part of a
time-sensitive overhaul of our budget system. Any suggestions would be
greatly appreciated!

Thanks,
Sierra

 

[Bernard Liengme]

Bit confusing. You summary sheet seems have the criteria in A and B but you
talk about B6 and C10. Let's get that sorted first.

Also it is going to be easier on the summary sheet to repeat the 222220 and
not have blank cells. We can hid duplicates later with conditional
formatting

Please come back with clarification - we DO want to help

 

[Sierra]

Thanks,

Sorry, I was just setting the sheets up as an example, my actual summery
sheet looks like this:

A B C D

6 611026 P/T Salaries 60000.00
7 3961 ?
8 3962 ?
9 3963 ?
10 3964 ?
11 3965 ?
12 3966 ?
13 3967
14 3968
15 39610
16 39611
17 36912
18 39613
19 39614
20 39615
21 39616
22 611028 Sessionals 38000.00
23 3961 ?
24 3962 ?
25 3963 ?
26 3964
27 3965
28 3966
29 3967
30 3967
31 3968
32 36912
33 39610
34 39611
35 39613
36 39614
37 39615
38 39616
etc., etc.

Column B holds the account numbers and column C holds the department
numbers. My example of B6 and C10 for criteria was trying to find the total
of expenses in account 611026 and within 3964

I can certainly enter the account numbers in each line, it just looked
cluttered to me. Thanks again and I look forward to your response!

Sierra

 

[Bernard Liengme]

I will assume the Data sheet (with name Data) looks like:That the summary sheet layout is
A B C

In C1 to sum all the L-values for the corresponding A and B value
=SUMPRODUCT(--(Data!$A$1:$A$1000=A1),--(Data!$B$1:$B$1000=C1), $L$1:$L$1000)
This can be copied down the column to complete the table.
Adjust the ranges as needed. Beware that SUMPRODUCT cannot use full-column
references such as A:A.

I duplicated the data in A to make it easier to write and copy the formula.
Bt I agree it looks tidier with out. So we will use Conditional formatting.
Select all the A after A1 (so A2:A1000 or whatever) column entries
Use Format Condition Formatting
Set the dialog to Formula is: =A2=A1 and then set the font colour to white
(or whatever you cell background is).
So now when you have in A2 the same values as in A1 (or if A3 the same as in
A2,,,,) the entry will be hidden


For more on SUMPRODUCT see
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[Sierra]

Thanks for your patience with my lack of clarity! The formula you have
proposed is the same one I've been trying to use, but the total keeps coming
out as zero.

When I evaluate the calculations, the first part (--(Data!$A$1:$A$1000=A1)
seems to be working fine, but the second part (Data!$B$1:$B$1000=C1) comes up
as FALSE for all rows, therefore '0', therefore my total on the summary sheet
is '0', this is where I'm at a loss.

Thanks,
Sierra

 

[Pete_UK]

I think your values in column B may be text, whereas the value you are
trying to match with is a number. A quick way of testing this is to
enter your second number as '3330 (i.e. with an apostrophe in front)
to see if this corrects matters. Of course, it could be the other way
round, with numerical data in your table and your sought value is
text.

Hope this helps.

Pete