Solved

CountIF Mutiple Conditions Spreadsheet Formula

Posted on 2012-03-20
16
383 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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

680 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