Multiple criteria formula needed

jberwald

Board Regular
Joined
May 3, 2005
Messages
205
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!
 

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."
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.
 
Upvote 0
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
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
<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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top