Solved

CountIF Mutiple Conditions Spreadsheet Formula

Posted on 2012-03-20
16
375 Views
Last Modified: 2012-04-03
I need to count the number of records in a sheet 'Leads' where the value in the column C is found in another sheet 'Current Inventory' column A


'Leads'!C:C

Stock#
MER360038
MER360038
MER360038
BMWZ36562
MER360038
BMWZ36562


'Current Inventory'!A:A

BMW
Stock
 BMWR25378
 BMWE75982
 BMWF56552
 BMWV13219
 BMWZ36562
 BMWN02467
 BMWC33263
 BMWG83141

Chevrolet
Stock
 CHE147687

Mercedes-Benz
Stock
 MER360038

MINI
Stock
 MINT53336

Porsche
Stock
 PORA75197

Volvo
Stock
 VOL048441
 VOL104979
 VOL170160
 VOL208405
 VOL273506
 VOL036618


Photos
Trim
Price
Description
Cost
Description
Cost
0
Comment
Question by:swedishmotors
  • 8
  • 5
  • 3
16 Comments
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
In 'Leads' sheet
In first row, (any empty cell on same row:  For example E1)
= COUNTIF (C1:C500 , 'Current Inventory'!A1 )

In second row, (any empty cell on same row:  For example E2)
= COUNTIF (C1:C500 , 'Current Inventory'!A2 )

HTH
0
 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
0
 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
I plan to put the formula in Summary!D9
0
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
in 'Current Inventory'!Q19

could you try

= COUNTIF ('Leads'!C4:C9 , A19)

?

It should put count = 4.
0
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
HTH = Hope this help

Ashok
0
 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
Sorry Ashok,
Do you have any other input?
0
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
No problem,

You need to figure out where you will place count answer
because # of models would be many and some models will have count of zero.

In your 'Current Inventory' sheet column A19, you have value
MER360038
and
if you place formula in Q19 (of 'Current Inventory' sheet)
as following
= COUNTIF ('Leads'!C4:C9 , A19)

The count will be 4 (which is number of times MER360038 repeats in other sheet).

HTH
Ashok
0
 
LVL 13

Expert Comment

by:Ashok
Comment Utility
Are you looking for one summary count?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
I want the summary count of all leads for current inventory.
I wish to place this on the 'Summary' sheet.

I have tried =COUNTIF ('Leads'!C4:C9 , 'Current Inventory'!A19)
on the summary sheet and = COUNTIF ('Leads'!C4:C9 , A19) in Q19

Both return 0
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
You want to do this in googledocs?

Try this formula

=Arrayformula(sum(if(isnumber(match(Leads!C4:C9,'Current Inventory'!A1:A100,0)),1)))

regards, barry
0
 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
Yes google docs

It seems the formula does not update the match count

I placed the formula in I18
the count should be 3, but it returns 2
0
 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
Odd, if I add  PORA75197 to C9 it updates to 3
0
 
LVL 50

Expert Comment

by:barry houdini
Comment Utility
Why should it be 3? are you excluding duplicates?

regards, barry
0
 
LVL 1

Author Comment

by:swedishmotors
Comment Utility
I do not understand the formula.  I do not want to exclude duplicates.
The total I want is 7 as there are 7 leads for which the Stock number is found in Current inventory.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
Comment Utility
OK my formula is correct for that purpose.....but it's not counting the MER360038 entries because they don't match - the entry in Current Inventory sheet A19 has a "leading space" so that means you don't get the required match with the entries in Leads sheet which don't have that leading space. Either remove that space or include a TRIM function in the formula to fix, i.e.

=Arrayformula(sum(if(isnumber(match(Leads!C4:C9,TRIM('Current Inventory'!A1:A100),0)),1)))

regards, barry
0
 
LVL 1

Author Closing Comment

by:swedishmotors
Comment Utility
Thank you for your help!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now