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!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

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

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
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

Board Regular
Joined
May 3, 2005
Messages
205

ADVERTISEMENT

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

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

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
<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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,635
Messages
5,512,528
Members
408,902
Latest member
VicRattlehead

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top