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.

 

[JackDanIce]

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)

 

[Aladin Akyurek]

=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]

I am using the sumproduct function:

Column A: Company
Column B: Category
SUMPRODUCT(('workbook'!A:A=A2)*('workbook'!B:B=B2)*('Workbook'!C:C="*truck*")*'workbook'!D:D)
This function returns "VALUE" error.
I am obviously using the wrong formula or missing something.

 

[Aladin Akyurek]

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.

 

[JackDanIce]

@Aladin Akuyrek as a side, I should use COUNTIFs more than SUMPRODUCT as aware it's faster, but with COUNTIFs is using entire column slower than SUMPRODUCT with defined range?

 

[Aladin Akyurek]

@Aladin Akuyrek as a side, I should use COUNTIFs more than SUMPRODUCT as aware it's faster, but with COUNTIFs is using entire column slower than SUMPRODUCT with defined range?

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.

 

[JackDanIce]

I learnt about double unary converting TRUE and FALSE into 1 and 0, so intuitively SUMPRODUCT for requirements like this made more sense, but will use COUNTIFs more often, especially if it's faster.

Thank you @Aladin Akyurek for suggesting

 

[Kthom019]

Thank you.
The formula returned a zero value

 

[Kthom019]

Thank you, formula also returned a zero value