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.

 

[Vtikhon]

Here is the formula that you can use:
=SUMPRODUCT(--($A$2:$A$4=$A2),--($B$2:$B$4=$B2),$C$2:$C$4)

However, I would use Pivot Table to summarize this type of data.

 

[Skipper66]

Thanks....I just tried it and I get a #VALUE! error in the cell. This has happened every time I've tried it.

 

[Vtikhon]

Would you be able to attach actual data you are working with?

 

[markmzz]

Or try this array formulas (use Ctrl+Shift+Enter and not only Enter):


Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>Month</TD><TD>Product type</TD><TD>Amount</TD><TD>Sep sales, Barley</TD><TD style="TEXT-ALIGN: right"></TD><TD>Month</TD><TD>Product type</TD><TD>Sales</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>Sep</TD><TD>Barley</TD><TD style="TEXT-ALIGN: right">$5.00 </TD><TD style="TEXT-ALIGN: right">$22.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD>Sep</TD><TD>Barley</TD><TD style="TEXT-ALIGN: right">$22.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD>Sep</TD><TD>Barley</TD><TD style="TEXT-ALIGN: right">$17.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Nov</TD><TD>Seeds</TD><TD style="TEXT-ALIGN: right">$12.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Nov</TD><TD>Seeds</TD><TD style="TEXT-ALIGN: right">$12.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>



<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">{=SUM(($A$2:$A$4="Sep")*($B$2:$B$4="Barley")*($C$2:$C$4))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>H2</TH><TD style="TEXT-ALIGN: left">{=SUM(($A$2:$A$4=F2)*($B$2:$B$4=G2)*($C$2:$C$4))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


</TD></TR></TBODY></TABLE>


Markmzz

 

[Skipper66]

I have several sheets on the workbook that each tie into each other. I'll see if I can attach the file.

 

[Skipper66]

[/URL]

The bottom sheet pulls data from the information on the top sheet to populate the fields.

I'll try a few of the ideas that have been posted, but this is what I'm working on.

I thank everyone for their time....I appreciate your help.

 

[Charlie45]

If the top sheet is in A1:C4


The bottom sheet is in A9:M15 then in cell B10 Enter

=SUMPRODUCT(--($A$2:$A$4=LEFT(B$9,3)),--($B$2:$B$4=$A10),$C$2:$C$4)

Copy down and over

 

[markmzz]

Or try this array formulas (use Ctrl+Shift+Enter and not only Enter):

Note: are two formulas (one in B9 and another in B13)

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Anticipated</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Net Product</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Net Product</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Close Date</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Category</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Volume</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Sep</TD><TD style="TEXT-ALIGN: center">Shelving</TD><TD style="TEXT-ALIGN: center">$32,000.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">Oct</TD><TD style="TEXT-ALIGN: center">Cabinets</TD><TD style="TEXT-ALIGN: center">$45,000.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">Jan</TD><TD style="TEXT-ALIGN: center">Consoles</TD><TD style="TEXT-ALIGN: center">$32,655.00 </TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">January</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">February</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">September</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">October</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">November</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">December</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Product</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Sales Volume</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Sales Volume</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Sales Volume</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Sales Volume</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Sales Volume</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #d8d8d8; FONT-WEIGHT: bold">Sales Volume</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD>Carriages</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="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD>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">$45,000.00 </TD><TD style="TEXT-ALIGN: center">$0.00 </TD><TD style="TEXT-ALIGN: center">$0.00 </TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Stell Solutions</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="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Consoles</TD><TD style="TEXT-ALIGN: center">$32,655.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="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Other</TD><TD style="TEXT-ALIGN: center">$0.00 </TD><TD style="TEXT-ALIGN: center">$0.00 </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></TR></TBODY></TABLE>Print


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B9</TH><TD style="TEXT-ALIGN: left">{=SUM(($A$3:$A$5=LEFT(B$7,3))*($B$3:$B$5=$A9)*($C$3:$C$5))}</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B13</TH><TD style="TEXT-ALIGN: left">{=SUM(($A$3:$A$5=LEFT(B$7,3))*ISERROR(MATCH($B$3:$B$5,$A$9:$A$12,0))*($C$3:$C$5))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

</TD></TR></TBODY></TABLE>

Markmzz

 

[Skipper66]

I've had to change the style of the sheet based on how they want the information to appear.

The green section populates based on information in other sheets in the workbook and will vary based on the input.

I've tried several of the suggestions here but still keep getting VALUE! error messages. I'm sure it's one of those deals where I'm missing one tiny thing and without that one thing, the whole formula blows up and won't work.

Until recently, I thought I knew enough about Excel to be dangerous....now however....well, I'm only a danger to myself. lol

Thanks again for your time and input.