IF formula with multiple criteria How??

[Skipper66]

Hi,

I'm desperately trying to write a formula that will calculate based on criteria from other columns.

Column A contains a month of the year. Column B has the type of product. Column C has a dollar amount.

I need to have a Column D that calculates a total dollar amount based on criteria in the first two columns.

A B C D(September sales, Barley)
Sep Barley $5.00 $22.00
Sep Barley $17.00
Nov Seeds $12.00

So I'd like to take column D and have it be written so that "if cells A1:A17 are "Sept", and Column B cells B1:B17 are Barley, then take the amount from column C based on that and add them together to give me a total.

I've tried SUMPRODUCT, IFSUM, SUM....arrays, etc. and I have to be missing something because none of it will work properly.

I appreciate any advice you have to give.

 

[markmzz]

Look at this:

Note: all formulas are array formulas. You have to entered with Ctrl+Shift+Enter and not only Enter.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Anticipated</td><td style=";">Net Product</td><td style=";">Net Product</td><td style="text-align: right;;"></td><td style="text-align: center;;">January</td><td style="text-align: center;;">February</td><td style="text-align: center;;">March</td><td style="text-align: center;;">April</td><td style="text-align: center;;">May</td><td style="text-align: center;;">June</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Close Date</td><td style=";">Category</td><td style=";">Volume</td><td style=";">Product</td><td style="text-align: center;;">Sales Volume</td><td style="text-align: center;;">Sales Volume</td><td style="text-align: center;;">Sales Volume</td><td style="text-align: center;;">Sales Volume</td><td style="text-align: center;;">Sales Volume</td><td style="text-align: center;;">Sales Volume</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">January</td><td style=";">Carriages</td><td style="text-align: center;;">$32,000.00 </td><td style=";">Carriages</td><td style="text-align: center;;">$32,000.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">June</td><td style=";">Cabinets</td><td style="text-align: center;;">$45,000.00 </td><td style=";">Cabinets</td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$45,000.00 </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">February</td><td style=";">Stell Solutions</td><td style="text-align: center;;">$43,000.00 </td><td style=";">Stell Solutions</td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$43,000.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Casework</td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Other</td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td><td style="text-align: center;;">$0.00 </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K3</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$A$3:$A$5=K$1</font>)*(<font color="Red">$B$3:$B$5=$J3</font>)*(<font color="Red">$C$3:$C$5</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K4</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$A$3:$A$5=K$1</font>)*(<font color="Red">$B$3:$B$5=$J4</font>)*(<font color="Red">$C$3:$C$5</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K5</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$A$3:$A$5=K$1</font>)*(<font color="Red">$B$3:$B$5=$J5</font>)*(<font color="Red">$C$3:$C$5</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K6</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$A$3:$A$5=K$1</font>)*(<font color="Red">$B$3:$B$5=$J6</font>)*(<font color="Red">$C$3:$C$5</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K7</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">$A$3:$A$5=K$1</font>)*ISERROR(<font color="Red">MATCH(<font color="Green">$B$3:$B$5,$J$3:$J$6,0</font>)</font>)*(<font color="Red">$C$3:$C$5</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz