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
'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
'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
ASKER
HTH,
this does not work for me.
both return 0
https://docs.google.com/spreadsheet/ccc?key=0AvApWss00vBedFVWY2ZOaXBZdGJ0N3Nsa05rNi1xQmc
this does not work for me.
both return 0
https://docs.google.com/spreadsheet/ccc?key=0AvApWss00vBedFVWY2ZOaXBZdGJ0N3Nsa05rNi1xQmc
ASKER
I plan to put the formula in Summary!D9
in 'Current Inventory'!Q19
could you try
= COUNTIF ('Leads'!C4:C9 , A19)
?
It should put count = 4.
could you try
= COUNTIF ('Leads'!C4:C9 , A19)
?
It should put count = 4.
HTH = Hope this help
Ashok
Ashok
ASKER
Sorry Ashok,
Do you have any other input?
Do you have any other input?
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
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
Are you looking for one summary count?
ASKER
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
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
You want to do this in googledocs?
Try this formula
=Arrayformula(sum(if(isnum
regards, barry
Try this formula
=Arrayformula(sum(if(isnum
regards, barry
ASKER
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
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
ASKER
Odd, if I add PORA75197 to C9 it updates to 3
Why should it be 3? are you excluding duplicates?
regards, barry
regards, barry
ASKER
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.
The total I want is 7 as there are 7 leads for which the Stock number is found in Current inventory.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help!
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