Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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
0
swedishmotors
Asked:
swedishmotors
  • 8
  • 5
  • 3
1 Solution
 
AshokCommented:
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
 
swedishmotorsAuthor Commented:
0
 
swedishmotorsAuthor Commented:
I plan to put the formula in Summary!D9
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
AshokCommented:
in 'Current Inventory'!Q19

could you try

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

?

It should put count = 4.
0
 
AshokCommented:
HTH = Hope this help

Ashok
0
 
swedishmotorsAuthor Commented:
Sorry Ashok,
Do you have any other input?
0
 
AshokCommented:
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
 
AshokCommented:
Are you looking for one summary count?
0
 
swedishmotorsAuthor 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
0
 
barry houdiniCommented:
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
 
swedishmotorsAuthor Commented:
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
 
swedishmotorsAuthor Commented:
Odd, if I add  PORA75197 to C9 it updates to 3
0
 
barry houdiniCommented:
Why should it be 3? are you excluding duplicates?

regards, barry
0
 
swedishmotorsAuthor 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.
0
 
barry houdiniCommented:
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
 
swedishmotorsAuthor Commented:
Thank you for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now