Multiple criteria formula needed

[jberwald]

I will eliminate a criteria from my question yesterday in a hope to get a much needed formula.

My master spreadsheet lists every county (Column D) in every state (Column B). Quarterly I need to enter our sales amounts into Column F of that spreadsheet. Because we do not sell in every county in every state this becomes a hunt and match exercise.

I download our info and cut and paste it into Column J (state), K (county), and L (amount).

I need a formula (in column F) that if: Column J says Alabama, it will look for matching counties in columns D and K - and when that criteria is met, enter the number from Column L, otherwise I need column F left blank.

Can anyone please help!

 

[hiker95]

jberwald,

Can we see screenshots of your Master workbook:
1. the "Quarterly I need to enter our sales amounts into Column F of that spreadsheet."
2. The worksheet (in your Master wrokbook) you "download our info" into.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[jberwald]

I am sorry, our firewall is restricting me. Here is an idea of what it looks like.

A B C D E F G H I J K L
Alabama Autauga Alabama Baldwin 133
Alabama Baldwin Alabama Cullman 55
Alabama Barbour Alabama Escambia 80


Columns B and D contain a list of EVERY state and EVERY county in the USA . Columns J and K list ONLY the states and counties we have sold merchandise in, with the amount in Column L

 

[rallcorn]

If you have Excel 2007 use this:
<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=411 border=0><COLGROUP><COL style="WIDTH: 308pt; mso-width-source: userset; mso-width-alt: 17536" width=411><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 308pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=411 height=17>=IF(ISERROR(SUMIFS(L:L,J:J,A2,K:K,B2)),"",SUMIFS(L:L,J:J,A2,K:K,B2))</TD></TR></TBODY></TABLE>

if older version of Excel use this:
<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=411 border=0><COLGROUP><COL style="WIDTH: 308pt; mso-width-source: userset; mso-width-alt: 17536" width=411><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 308pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=411 height=17>=SUMPRODUCT(--(J2:J4=A3),--(K2:K4=B3),L2:L4)</TD></TR></TBODY></TABLE>

 

[jberwald]

I appreciate your taking the time to help. I am working in Excel 2007. I have no usable data in Column A - so your formula does not work (sorry I couldn't post a screenshot) and I don't understand enough of what you are trying to do to transpose column letters.

Columns B and J will have the states and columns D and K the counties. I need the number from Column L in column F if all criteria is met.

 

[jberwald]

Here is the question in English:

If Column J=Alabama, look to see if Column D and K match. If they do enter the corresponding number from Column L

 

[rallcorn]

<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=411 border=0><COLGROUP><COL style="WIDTH: 308pt; mso-width-source: userset; mso-width-alt: 17536" width=411><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ffffff; BORDER-TOP: #ffffff; BORDER-LEFT: #ffffff; WIDTH: 308pt; BORDER-BOTTOM: #ffffff; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=411 height=17>=IF(ISERROR(SUMIFS(L:L,J:J,B2,K:K,D2)),"",SUMIFS(L:L,J:J,B2,K:K,D2))</TD></TR></TBODY></TABLE>
Let's walk thru sumifs
L:L is column it will sum
J:J is the column in which it is looking for B2 (state to match)
K:K is the column in which it is looking for D2 (county to match)

Have you ever noticed the formula "help" box that pops up below the cell into which you're inputting the formula? It tells what is expected for each portion of the formula. Or, if you enter formulas using fx, there are "helps" if you look over the entire "function arguments" box.