Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • 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
Industry Leaders: 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!

 
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

Featured Post

Technology Partners: 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!

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