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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,438
Members
431,879
Latest member
KiwDaWabbit

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
Top