Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Count if two columns match different criteria

How can I count two columns with different criterias. In one column if it
contains 737 and if another column contains LAX, how can I count them if they
only meet this criteria? Thanks for the help.


Post your answer or comment

comments powered by Disqus
I have one column containing clinics. The other column may contain Follow Up
appointments. I'm trying to count the matched sets, i.e. all nuclear
medicine with follow ups, Neurology with Follow Ups, and so on. Here is the
string that is showing the correct number when tested but shows "0" in the
cell. =COUNT(IF((Sheet1!D2200="Nuclear Med.")*(Sheet1!G2:G200="Follow
Up"),Sheet1!H2:H200)). The H column has this string: =IF(G19:G217="Follow
Up",1,0) I am trying to count this number if the other two criteria is met.
It doesn't work. Any suggestions?

I have one column containing clinics. The other column may contain Follow Up
appointments. I'm trying to count the matched sets, i.e. all nuclear
medicine with follow ups, Neurology with Follow Ups, and so on. Here is the
string that is showing the correct number when tested but shows "0" in the
cell. =COUNT(IF((Sheet1!D2:D200="Nuclear Med.")*(Sheet1!G2:G200="Follow
Up"),Sheet1!H2:H200)). The H column has this string: =IF(G19:G217="Follow
Up",1,0) I am trying to count this number if the other two criteria is met.
It doesn't work. Any suggestions?

hello,
I have a formula problem. Excel 2007.
I need to count 2 columns, with 2 different criteria, columns on on a different spreadsheet, and have the results end on my last page for graphing purposes.

This worked on the first column.

=COUNTIFS(‘RAZ00302′!R:R,”WRONG STATUS”)
After that it starts erroring.

This is tracking one of the columns on the razo302 sheet but I also need to track column t. 3 items in column t need to be picked: CPSUB or B7SUB or E9SUB

If colun R is wrong status and column T are any of these: it should be counted.
(Tried this and it gives crazy errors)

=COUNTIFS(‘RAZ00302′!R:R,”WRONG STATUS”)+SUM(COUNTIF(‘RAZ00302′!T:T,{“CPSUB”,”B7SUB”,”E9SUB”}))

PLEASE ADVISE ASAP—I AM SO EXCITED THAT i FOUND YOUR WEBSITE.
thank you!!!
angeliz13

I have a spreadsheet where I want to sum a certain column if two other columns meet a specific criteria.

For instance, I want to sum the Range from E3:E33

If any cell in Range A3:A33 = "Bach."
and
If any cell in Range C3:C33 = "F"

How may I accomplish this using an Excel formula?

Please Help!
Thanks

Hello!

I'm trying to count the result of two columns match.

=SUMPRODUCT(('Commerce'!$H$1:$H$1000=$B5)*('Commerce'!$J$1:$J$1000=D$4))

Work's fine like this! But the thing is, it's comparing *exactly* with the B5 and D4 cells. Actually I want to make a lookup in the cell to see if there's the word in it, like:

=COUNTIF('Commerce'!$H:$H;"*" & B9 & "*")

Do you guys know if it's possible?

Thanks,
efilipe.

Hi I'm trying to do something similar to the Vlookup function (or Index/Match method) but with two or more parameters to match instead of just one. I want to returnn the Row where two columns match the input parameters. Using Vlookup is a problem because it only returns the first one.

Is the standard method to us Vlookup for one parameter, then check the second and if it doesn't match reduce the search field, find the next and check the second again etc? That would work but it would be much simpler if there's a 2 parameter function. Thanks for the help.

How can I get Excel to compare two columns of numbers? For example, for each
number in the first column I want to count how many in the second column
contain the same number.
Thanks

macro - show rows ONLY if two columns = each other AND......
Hello Excel Swammis!

I am in need of your assistance again.

I have an Excel report set up as follows:

Col D - Order #'s
Col G - part #'s
Col H - warehouse code for each part # ("N", "M", etc...)
Col K - Qty of part # ordered
Col L - Qty reserved for that part # on the order

List of orders starts on row 9.

If a particular line on the order is filled, then Col K = Col L for that row.
When the whole order is filled, then Col K = Col L for all rows on that order.

What I need is a macro that identifies FULL ORDERS and hides all other rows. So, if Col K = Col L for all rows adjacent to same order #'s in Col D (AND if Col H has value of "N" or "M" for warehouse code), it will be visible. Any orders with even ONE unfilled line will be hidden.

I am also looking for the reverse of this for a seperate macro. IE: Any orders with even ONE line not completely filled, I want all rows for those orders to be visible and all else hidden (again, only if Col H = "N" or "M").

Is this possible?

Let me know if more info needed, I'll be in front of my monitor for several more hours.

Good Day, I am new to macros and have a bit of knowledge on excel. I woul
like to copy a Value from a cell if two fileds on different sheets math.

Good Day, I am new to macros and have a bit of knowledge on excel. I woul
like to copy a Value from a cell if two fileds on different sheets math.

Can someone please help me with a formula or vb code to find all of the rows that match another row on two columns? I need to be able to delete all duplicate rows, but they're only duplicates if two columns match. For example

ROW   A  
 B
 1    1    a
 2    2    a
 3    3    b
 4    4    b
 5    1    a
Only rows 1 and 5 would match.

Greetings,

I work in a call center and work with large spread sheets that sometimes contain more than 25,000 rows of information regarding individual phone interactions with clients. One column contains a time stamp from when a client's profile was created. The adjacent column contains a time stamp for the individual conversation. If the information in two adjacent cells matches, that indicates that the conversation was with a first-time caller. I need a formula that will count the number of times two adjacent cells within these two columns match one another.

If at all possible, I’d like to avoid an approach that requires creating another column, inserting a formula and dragging down to the final row. In the example workbook I’ve posted, there are 9 rows whose data is matching. A formula that could be placed in a single cell and calculate those nine instances would be excellent.

Thanks so much for your help.

So I'm trying to add up two columns based on criteria from each to give me a total. From below I would like to add up all that have say CMDY and all that have a Verified time larger then 16:00. The answer should be 12, 2 from CMDY and 10 greater then the verified time of 16:00.

I have tried =sumproduct and =countif but neither seems to give me the results I'm looking to get. Maybe I'm missing something in my formulas but I don't get a number I get an error. I attached a .xls what what it looks like in excel. I cleaned the attachment up a little so the formula below has a little different range.

This is what I have been trying to get working but
=SUMPRODUCT(G20:G144=CMDY)*(N20:N144>=Verified: 09/15/10 16:00))

006 54989A HD Forte 9.30 SSEA AENN 16:00-24:00 09/15/10 19:15:30 09/19/10 Verified: 09/15/10 19:16

016 54989A HD Forte 9.30 SSEA CMDY 06:00-24:00 09/15/10 16:18:30 09/19/10 Verified: 09/15/10 16:22

020 54989A HD Forte 9.30 SSEA CMDY 16:00-24:00 09/15/10 21:40:30 09/19/10 Verified: 09/15/10 21:33

024 54989A HD Forte 9.30 SSEA CNNN 06:00-24:00 09/15/10 22:31:30 09/19/10 Verified: 09/15/10 22:44

028 54989A HD Forte 9.30 SSEA CNNN 16:00-24:00 09/15/10 20:50:30 09/19/10 Verified: 09/15/10 20:56

066 54989A HD Forte 9.30 SSEA FSNW 16:00-24:00 09/15/10 22:45:00 09/19/10 Verified: 09/15/10 22:31

070 54989A HD Forte 9.30 SSEA FXXX 06:00-24:00 09/15/10 15:55:00 09/19/10 Verified: 09/15/10 15:57

082 54989A HD Forte 9.30 SSEA FXNC 16:00-24:00 09/15/10 19:20:30 09/19/10 Verified: 09/15/10 19:19

086 54989A HD Forte 9.30 SSEA HIST 06:00-24:00 09/15/10 08:45:30 09/19/10 Verified: 09/15/10 08:42

094 54989A HD Forte 9.30 SSEA LIFE 06:00-24:00 09/15/10 06:20:00 09/19/10 Verified: 09/15/10 06:28

107 54989A HD Forte 9.30 SSEA SPKK 06:00-24:00 09/15/10 07:29:30 09/19/10 Verified: 09/15/10 07:28

123 54989A HD Forte 9.30 SSEA TBSC 06:00-24:00 09/15/10 13:45:00 09/19/10 Verified: 09/15/10 13:42

127 54989A HD Forte 9.30 SSEA TBSC 16:00-24:00 09/15/10 18:27:30 09/19/10 Verified: 09/15/10 18:16

127 54989A HD Forte 9.30 SSEA TBSC 16:00-24:00 09/15/10 19:45:30 09/19/10 Verified: 09/15/10 19:46

139 54989A HD Forte 9.30 SSEA TNTT 06:00-24:00 09/15/10 08:45:30 09/19/10 Verified: 09/15/10 08:40

143 54989A HD Forte 9.30 SSEA TNTT 16:00-24:00 09/15/10 16:31:30 09/19/10 Verified: 09/15/10 16:15

I have two columns, one with dates, the other with text. I want to count if the date column matches xx, and if text column contains certain text. If I want to find out how many rows fit the criteria of: in April and contains OG, I should get the answer of 3. If I didn't have the problem of some cells contain more than one value, i.e. simply just OG or OR or SS, then this formula works:{=SUM((MONTH(range)=4)*(range="OG"))}, which returns 1, and not what I want. Help please!

Column A Column B
April-21-11 OR / OG
April-21-11 OR / OG
May-02-11 OG
June-06-11 SS
April-21-11 SS
April-21-11 OR
May-02-11 SS
June-06-11 SS
April-21-11 OG
April-21-11 OR
May-02-11 OG
June-06-11 OG

I have a list in the name manger that referenced from a different workbook and has two columns.

This code works but it scrolls all the way down to unused cells

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I've modified the code to offset to only used cells (this list will keep on adding up)

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help, thanks in advance!

Hey all

I want to create a macro that will allow me to compare two columns of data from 2 different worksheets and then if it finds a match creates a new column in the 2nd worksheet containing the value from the 2nd column in the first worksheet which corresponds to the first matching column. For Example

Worksheet 1 Worksheet 2

Column A Column B Column A New Column
On 1 Off 2
Off 2 Reset 3
Reset 3 On 1

So I kinda tried to create a graphical representation of what I want it to look like. Basically I want to compare Column A of worksheet1 with Column B of worksheet 2, with Column A from worksheet 2 not necessarily in the same order as Column A in worksheet 1. Column B in worksheet 1 is the corresponding numerical value for the text in Column A. I would like the macro to compare both Column As and if there is a match return the corresponding value in Column B to a New Column in worksheet 2. I'm kinda new at VB, so any help with be great.

After 3 days, I'm stumped.

I have a spreadsheet (where data is entered) that needs to be counted and summarized in subsequent pages - and I cannot figure out how to formulize the following:

1) If Cells C8:C571 indicate "1" (number 1),
2) And Cells O8:O571 indicate "X" (letter X),
3) Then I need to count how many times those two columns match up, (an actual number of times those conditions occur in those two columns),
4) And add the sum total of the currency figure that is in Cells J8:J571 when those two conditions are met (in columns C and O).

Forgive me if this sounds nuts - I've been working on this for days and cannot find an answer to this!

Appreciate ANY assistance!

I have two Columns A and B, both with some alphanumeric text and other descriptions (mixed), i want a code/formula to extract value of Column C,
If in a row of column A (which is long text contain Alphanumeric bits) contains word "ABC3" and if in same row of Column B contain word "CDE6" .(Which is again long text containing Alphanumeric bits.) then display value of same row column C

Please note: Column B might contain word "CDE6" in several Rows but will only match once with "ABC3"

any help will be highly appreciated.

Hey guys,
Is it possible to make excel count colum D1 if colum A1 matches colum z1 for example?? Please help

Hi, Grateful if I can get guidance on a formula. I have looked in multiple threads on this topic, but haven't cracked the nut.

My Purpose:
Identify and list duplicate AND multiple duplicate entries by comparing two columns where sorting the dataset is not an option.

The Dataset:
Compare data in column "A" as "The Source" containing multiple unique (non-duplicate) numeric entries against column "B" with duplicates and multiple duplicate entries.

I've played with Index, Match and Small functions to no avail. Below is an example of what I am trying to achieve:
A B Result I'm looking for 456 456 456 234 234 234 123 891 456 456 234 234 123 123 567 456 456

Hi,

I have the following data

ABCD1MarginRevenue22000040023150005504120006005100005006500010072000200

I need to count number of rows in which different criteria for both Margin and Revenue are met

Suppose I want to find the number of rows in which Margin>=10000 and Revenue>500..i have used the formula
=SUMPRODUCT(--(B3:B8>=10000),--(C3:C8>500))
in D2 which is giving me the correct result-2

Now suppose I want to find the number of rows in which Margin is between 9000 and 13000 and Revenue >500

then what are the changes that need to be made in the formula?

I am doing a SUM if formula and want the sum to come from two columns.

If Column A meets the criteria then sum the data in Column B & C.

I am trying to do a count, based on two columns of data.

Column A contains the week number
Column B contains a product number

I want to count the instances of a product number, within a given week. I have a cover sheet, which has a list of product numbers, and the week number currently being reported, I would like to reference these to produce the count.

Sheet 1

Row Column A Column B
1 Week 1 ZI123
2 Week 2 ZI124
3 Week 2 ZI124
4 Week 2 PO999
5 Week 2 PO999

Sheet 2

Row Column A Columb B
1 Product ID Week 2
2 ZI124 2
3 PO999 2

Apologies this isn't on a proper sheet, restrictions at work prevent me putting together a sheet to give as example. I hope this conveys what I require, and am stuck on.

What I need is for cell B2 on Sheet 2, to count the two columns above. So far I've had little success.

I would normally add in an extra column to do a concatenate, and count the results of that, unfortunately, this isn't an option in this case, the data must remain the same due to contractual issues.

All help is appreciated.

Im looking for help on a Macro.

If two parrallel cells in column D & G equal "0" then highlight that row with ======== (Thin Horizontal stripe)

Example:

D2 and G2 both = "0" ( Macro Applies. Row 2 is highlighted with Thin Horizontal Stripe).

D2 and G3 both = "0" (Macro does not apply since they are in different rows)

D2= 1 G2=0 (Macro does not apply since they both dont meet condition.)


No luck finding an answer? You could always try Google.