SUMIF + Range Multiply.

[Angarsk]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hello.

I have a list with column A: Dates, B: Lenghts, C: Copies Drinters.

I would like to have a formula that searches all dates that match the date on a given cell and multiply the matchng Lengths by their number of copies (if A matches a certain date, multiply B * C and add all resoults).

My current formula: =SUMIF(A$5:A$200, V5, B$5:B$200)

Sums the Lenghts, but I need each lenght to be multiplied.

Could you help me with that?

...on a second question, could I also have my formula multiply and sum resoults that match both, the date (A) and the printer (D)?

Thank you very much.

 

[John McGhie]

See if this gets you closer:

http://office.microsoft.com/en-us/excel/HA010872901033.aspx

Cheers

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Hello.

I have a list with column A: Dates, B: Lenghts, C: Copies Drinters.

I would like to have a formula that searches all dates that match the date on
a given cell and multiply the matchng Lengths by their number of copies (if A
matches a certain date, multiply B * C and add all resoults).

My current formula: =SUMIF(A$5:A$200, V5, B$5:B$200)

Sums the Lenghts, but I need each lenght to be multiplied.

Could you help me with that?

...on a second question, could I also have my formula multiply and sum
resoults that match both, the date (A) and the printer (D)?

Thank you very much.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Angarsk]

Thank you very much... apparently, it didn't help. I know and used array formulas before (only the single cell though, now I know about the multi-cell). But I tried to add the array B:5:B200*C5:C200 to the end of my SUMIF formula and it didn't work.

Thanks for your interest though.

 

[John McGhie]

You wouldn't be able to add that formula onto the end, you would have to
"replace" what you had with an array formula.

Cheers

Thank you very much... apparently, it didn't help. I know and used array
formulas before (only the single cell though, now I know about the
multi-cell). But I tried to add the array B:5:B200*C5:C200 to the end of my
SUMIF formula and it didn't work.

Thanks for your interest though.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]

 

[Angarsk]

Good... I will dig more into it. Thank you