# Multiple criteria formula needed

#### jberwald

##### Board Regular
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.

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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."

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

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

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>

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.

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

<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.

Replies
4
Views
128
Replies
1
Views
213
Replies
3
Views
208
Replies
13
Views
260
Replies
1
Views
199

1,216,487
Messages
6,130,943
Members
449,608
Latest member
jacobmudombe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back