Solved

# CountIF Mutiple Conditions Spreadsheet Formula

Posted on 2012-03-20
375 Views
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

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
Question by:swedishmotors
• 8
• 5
• 3

LVL 13

Expert Comment

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

0

LVL 1

Author Comment

I plan to put the formula in Summary!D9
0

LVL 13

Expert Comment

in 'Current Inventory'!Q19

could you try

?

It should put count = 4.
0

LVL 13

Expert Comment

HTH = Hope this help

Ashok
0

LVL 1

Author Comment

Sorry Ashok,
Do you have any other input?
0

LVL 13

Expert Comment

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

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

HTH
Ashok
0

LVL 13

Expert Comment

Are you looking for one summary count?
0

LVL 1

Author Comment

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

You want to do this in googledocs?

Try this formula

regards, barry
0

LVL 1

Author Comment

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

0

LVL 50

Expert Comment

Why should it be 3? are you excluding duplicates?

regards, barry
0

LVL 1

Author Comment

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

barry houdini earned 500 total points
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.

regards, barry
0

LVL 1

Author Closing Comment

0

## Featured Post

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…