SUMIF with multiple criteria

**penri0_0** · 05-18-2006, 10:05 AM

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.

Bernard Liengme · 05-18-2006, 10:25 AM

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" <penri0_0.280b2m_1147961400.8731@excelforum-nospam.com> wrote in
message news:penri0_0.280b2m_1147961400.8731@excelforum-nospam.com...
>
> 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
>

Bondi · 05-18-2006, 10:30 AM

Hi,

Maybe you can use sumproduct()

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

Regards,
Bondi

**penri0_0** · 05-18-2006, 10:57 AM

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?

Bondi · 05-18-2006, 11:35 AM

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