My brain has gone into meltdown and i need help with a formula that will
look up data in two columns and if the criteria is met in column A it
will sum the adjacent cells in column B

Column A has time elapsed from invoice date to payment received (in a
number format, not date)
Column B has the value of said invoice

Using the criteria below i want a sum total of all invoices that were
paid in each range. I can get it to do the sum for all invoices paid
under 30days easily, but all the ones i've tried that include =30<60
etc fail.
0-30
31-60
31-90
91-120
121+

Where am i going wrong? I've done it before but i just can't recall
what i did...

any suggestions appreciated.


--
penri0_0


------------------------------------------------------------------------
penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947
View this thread: http://www.excelforum.com/showthread...hreadid=543296

How about using a Pivot Table?
OR use the FREQUENCY function?
The bin would have number 30,60,90,120 and a blank value at the end for over
120.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"penri0_0" wrote in
message ...

My brain has gone into meltdown and i need help with a formula that will
look up data in two columns and if the criteria is met in column A it
will sum the adjacent cells in column B

Column A has time elapsed from invoice date to payment received (in a
number format, not date)
Column B has the value of said invoice

Using the criteria below i want a sum total of all invoices that were
paid in each range. I can get it to do the sum for all invoices paid
under 30days easily, but all the ones i've tried that include =30<60
etc fail.
0-30
31-60
31-90
91-120
121+

Where am i going wrong? I've done it before but i just can't recall
what i did...

any suggestions appreciated.


--
penri0_0


------------------------------------------------------------------------
penri0_0's Profile:
http://www.excelforum.com/member.php...o&userid=28947
View this thread: http://www.excelforum.com/showthread...hreadid=543296

Hi,

Maybe you can use sumproduct()

=SUMPRODUCT(--(A1:A1030),--(A1:A10<61),B1:B10)

Regards,
Bondi

Thanks for both suggestions. Pivot table not right for this piece of
work but thanks anyway.

SumProduct did the job though! What are the dashes between parenthesis?
Without them the formula returned 0, but with them it worked. What do
they do?


--
penri0_0


------------------------------------------------------------------------
penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947
View this thread: http://www.excelforum.com/showthread...hreadid=543296

Hi,

They make the results in to numerals so the summing thingy can take
place.

Result without "--" would be TRUE, with "--" thingies would be 1

Regards,
Bondi

Thanks Bondi

Thats two things i've learned today - almost worth coming in the
office! ;)


--
penri0_0


------------------------------------------------------------------------
penri0_0's Profile: http://www.excelforum.com/member.php...o&userid=28947
View this thread: http://www.excelforum.com/showthread...hreadid=543296