COUNTIF - Using 2 columns or more ?? 2

[BDNH]

I have read several questions similar to this one but I can't seem to solve my problem.

I have a table of steel fittings that looks like this

Size(in) WT(in) Type Description

10 0.250 45 ELL
10 0.500 90 ELL
24 0.500 TEE
12 0.500 90 ELL
16 0.375 90 ELL
24 0.375 90 ELL

I have more than 200 records and need Excel to calculate the qty. of fittings using any certain criteria.

For example, How could I calculate How many

Size 10, 0.500 wt, 90 ELLs I have.

This is probably real simple for a lot of you out there, so please help!

 

[xlbo]

you need an array formula:
Say your data is on sheet2 in columns A,B,C,D - 1 to 100 in each case

In sheet1, A1 enter the Size
In A2, enter the WT
In A3, enter the type
In A4, enter the description

in A5 enter:
=SUM((sheet2!A1:A100=A1)*(sheet2!B1:B100=A2)*(sheet2!C1:C100=A3)*(sheet2!D1100=A4))

enter this using CTRL+SHIFT+ENTER

OR
add an extra field called CTR and enter a 1 in every cell. then, whack a pivot table over the lot and stick the 4 fields as PAGE fields with only SUM of CTR as your data field

As you change the combination of page fields, they will act like criteria and you will see the number of items change in the data field Rgds
Geoff
Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so. Douglas Adams

 

[SkipVought]

Hi,
Another way is to use AutoFilter to select your criteria and use the SUBTOTAL function 2 to 3 for COUNT or COUNTA respectively on a column

a count of all visible values in column a would be...

=SUBTOTAL(2,A2:A65536)

hope this helps Skip,
Skip@theofficeexperts.com

 

[DaleWatson123321]

BDNH,

Yet another option is to use Excel's VERY powerful "database" functions - e.g. =DSUM, DCOUNTA, etc.

These formulas use criteria to allow you to be VERY specific as to which records to add or count, etc.

There is also an option to extract out a copy of the "records behind the totals" - to a separate sheet, or alternatively to filter the records in place.

Excel's help on this topic is not great, so if you would like help with this option, I'm prepared to help get you "jump started". The best (easiest & fastest) method is to email your file (replacing sensitive data with fictitious data that still reflects the type of data you're working with, if necessary).

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[BDNH]

Thanks guys I really appreciate your help!

I tried xlbo's array and it worked great! Thanks xlbo

Thanks to you also skipVought I haven’t tried this yet, but I will. I work with many tables like this and I want to learn all I can about how to use them better.

DaleWatson123321, I will send you a partial table so that I can try your option as well. I want any information that will make my life easier.

This is the first question I’ve posted – you guys made it easy, Thanks again

 

[VBAjedi]

BDNH,

Don't forget to give xlbo a star! It's a fast way to say "thank you" to the people that help you here at Tek-Tips. Just click the "Mark this post as a helpful/expert post!" link on his post and confirm it.

Thanks,

VBAjedi

 

[BDNH]

Dale,

The table you sent back to me by email is fantastic!
Great Stuff! You’re a magician with Excel

Thanks a million