Solved

CountIF Mutiple Conditions Spreadsheet Formula

Posted on 2012-03-20
16
386 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
16 Comments
 
LVL 13

Expert Comment

by:Ashok
ID: 37743859
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
ID: 37743993
0
 
LVL 1

Author Comment

by:swedishmotors
ID: 37743998
I plan to put the formula in Summary!D9
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 13

Expert Comment

by:Ashok
ID: 37744081
in 'Current Inventory'!Q19

could you try

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

?

It should put count = 4.
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37744229
HTH = Hope this help

Ashok
0
 
LVL 1

Author Comment

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

Expert Comment

by:Ashok
ID: 37744266
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
ID: 37744311
Are you looking for one summary count?
0
 
LVL 1

Author Comment

by:swedishmotors
ID: 37744353
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
ID: 37744368
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
ID: 37744549
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
ID: 37744558
Odd, if I add  PORA75197 to C9 it updates to 3
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 37744625
Why should it be 3? are you excluding duplicates?

regards, barry
0
 
LVL 1

Author Comment

by:swedishmotors
ID: 37744839
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
ID: 37745248
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
ID: 37803493
Thank you for your help!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

615 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