Formula to search 3 or more criteria, then count text

[Kthom019]

I need help putting together a formula that will do the following:
1. Search for company name "ABC" in column A, then
2. Search for product category "Books" in column B, (there a 6 different product categories that I will be searching for), then
3. Search for product distribution type "truck" in column C, then
4. If the above criteria are met then, count the "alphanumeric codes" in column D
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Category[/TD]
[TD]Distribution Type[/TD]
[TD]Codes[/TD]
[/TR]
[TR]
[TD]Bookran[/TD]
[TD]Book[/TD]
[TD]Truck[/TD]
[TD][TABLE="width: 91"]
<colgroup><col></colgroup><tbody>[TR]
[TD]AP6802400[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Telstar[/TD]
[TD]Book[/TD]
[TD]Cart[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]IM1338917[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Major[/TD]
[TD]Pen[/TD]
[TD]Gate[/TD]
[TD]CH3117672[/TD]
[/TR]
[TR]
[TD]Bookran[/TD]
[TD]Book[/TD]
[TD]Truck[/TD]
[TD]CS6540201[/TD]
[/TR]
[TR]
[TD]Major[/TD]
[TD]Pen[/TD]
[TD]Gate[/TD]
[TD]TN9358217[/TD]
[/TR]
</tbody>[/TABLE]

I hope the information above is sufficient, I am creating a reporting sheet to pull data from this detailed sheet with thousands of records.

Please assist soonest; thanks.

 

[Aladin Akyurek]

Thank you, formula also returned a zero value

Which formula? And what is your Excel version?

 

[Kthom019]

Thank you very much, I achieved success with the first formula. I will try the SUMPRODUCT formula as well.

 

[Kthom019]

Formula 1. "COUNTIFS" worked; thank you. I will try the SUMPRODUCT as well.

1. COUNTIFS(A2:A1000,"x",B2:B1000,"y",C2:C1000,"z")

2. SUMPRODUCT(--(A2:A1000="x"),--(B2:B1000="y"),--(C2:C1000="z"))

Using dynamic named ranges, number of records sufficiently large...

3. COUNTIFS(Arange,"x",Brange,"y",Crange,"z")

4. SUMPRODUCT(--(Arange="x"),--(Brange="y"),--(Crange="z"))

5. COUNTIFS(A:A,"x",B:B,"y",C:C,"z")

6. SUMPRODUCT(--(A1:An="x"),--(B1:Bn="y"),--(C1:Cn="z"))

1 and 3 will be faster than their equivalents, i.e. 2 and 4.

If n of [6] is not larger than a limit, it will be faster than [5]. I'm not sure what that n would be.

 

[Kthom019]

I tried and it returned a zero value.

Welcome to the board. Try:

=SUMPRODUCT(--($A$1:$A$500="ABC"),--($B$1:$B$500="Books"),--($C$1:$C$500="truck"),$D$1:$D$500)

 

[Kthom019]

This formula returned a zero value. I am using excel 2016.

=COUNTIFS($A:$A,$F1,$B:$B,$G1,$C:$C,$H1,$D:$D,"?*")

where F1 houses a company of interet like Major, G1 a product category like Book, and H1 a distribution type like Truck.

 

[Kthom019]

Yes, the COUNTIF formula worked; thanks Aladin.

Use COUNTIFS which is faster: See post #3 .

If you can't, try:

=SUMPRODUCT(--($A:$A=$F1),--($B:$B=$G1),--($C:$C=$H1),--ISNUMBER(SEARCH("?*",$D:$D))

Try to avoid whole column references in a SumProduct formula for reasons of efficiency.

 

[Kthom019]

Thank you all ever so much for the quick replies and suggestions; the COUNTIF function worked pretty well.
I will continue to try the SUMPRODUCT.

I need help putting together a formula that will do the following:
1. Search for company name "ABC" in column A, then
2. Search for product category "Books" in column B, (there a 6 different product categories that I will be searching for), then
3. Search for product distribution type "truck" in column C, then
4. If the above criteria are met then, count the "alphanumeric codes" in column D
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company Name[/TD]
[TD]Category[/TD]
[TD]Distribution Type[/TD]
[TD]Codes[/TD]
[/TR]
[TR]
[TD]Bookran[/TD]
[TD]Book[/TD]
[TD]Truck[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]AP6802400[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Telstar[/TD]
[TD]Book[/TD]
[TD]Cart[/TD]
[TD][TABLE="width: 91"]
<tbody>[TR]
[TD]IM1338917[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Major[/TD]
[TD]Pen[/TD]
[TD]Gate[/TD]
[TD]CH3117672[/TD]
[/TR]
[TR]
[TD]Bookran[/TD]
[TD]Book[/TD]
[TD]Truck[/TD]
[TD]CS6540201[/TD]
[/TR]
[TR]
[TD]Major[/TD]
[TD]Pen[/TD]
[TD]Gate[/TD]
[TD]TN9358217[/TD]
[/TR]
</tbody>[/TABLE]

I hope the information above is sufficient, I am creating a reporting sheet to pull data from this detailed sheet with thousands of records.

Please assist soonest; thanks.

 

[Aladin Akyurek]

Yes, the COUNTIF formula worked; thanks Aladin.

You are welcome. Both should work... See:
https://www.dropbox.com/s/0r40e8eg9...3 or more criteria, then count text.xlsx?dl=0

But as said, the formula with COUNTIFS should be preferred.