# CountIF Mutiple Conditions Spreadsheet Formula

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
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sr. Software EngineerCommented:
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
Author Commented:
Author Commented:
I plan to put the formula in Summary!D9
Sr. Software EngineerCommented:
in 'Current Inventory'!Q19

could you try

?

It should put count = 4.
Sr. Software EngineerCommented:
HTH = Hope this help

Ashok
Author Commented:
Sorry Ashok,
Do you have any other input?
Sr. Software EngineerCommented:
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
Sr. Software EngineerCommented:
Are you looking for one summary count?
Author Commented:
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
Commented:
You want to do this in googledocs?

Try this formula

regards, barry
Author Commented:

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
Author Commented:
Commented:
Why should it be 3? are you excluding duplicates?

regards, barry
Author Commented:
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.
Commented:
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

Experts Exchange Solution brought to you by