Help please!

[Guest]

I've been working on a formula for adding expenses using multiple criteria
for about nine hours now, and I would realy appreciate some help! Here's my
situation:

Summary sheet:
A B C
1 222220 3330 (Total of data from other worksheet where row has A
and B)
2 222220 3333
3 222229 33353
4 222229 3330

Data 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

The data goes on for about 1500 lines, so I would really like to find a
formula that is something of a sumif, true/false, etc. that will give me the
results I need. I've came close, but all I get is zero...literally!

If anyone has any suggestions it would be greatly appreciated!

Thanks,
Sierra

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(Data!$A$1:$A$2000=A1), --(Data!$C$1:$C$2000=B1),
Data!$L$1:$L$2000)

 

[Bernard Liengme]

On the Summary sheet in C1
=SUMPRODUCT(--(Sheet2!$A$1:$A$1500=A1),--(Sheet2!$C$1:$C$1500=B1),$L$1:$L$1500)

If the second sheet has spaces in its name use 'Data Sheet' with single
quotes around worksheet name. I like to make these formulas by typing until
I get to the range {so I type =SUMPRODUCT(--( } then use the mouse to
select the range on the second sheet.

Adjust range as needed but SUMPRODUCT cannot use full column as in A:A

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

 

[T. Valko]

Try this:

=SUMPRODUCT(--(Data!A$1:A$1500=A1),--(Data!C$1:C$1500=B1),Data!L$1:L$1500)

 

[Guest]

Thank you so much to everyone that responded! Seeing as all three had the
same answer I think I've got a good shot at getting this to work!

I'm working part-time on a large budget and won't be able to test these
formulas out until next Wednesday, so I'll let you all know if this does it
for me. Thanks again, you're all life savers!

 

[Guest]

Hi all,

Thank you again for your help, but I tried the formuals and still can't get
them to work. I entered:

=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.

Any suggestions?

Thanks,
Sierra