Formula based on Cell content

[Outdoorsman80]

I have a spread sheet with several columns:
[TABLE="width: 683"]
<tbody>[TR]
[TD="class: xl68, width: 122"]Date Purchased[/TD]
[TD="class: xl67, width: 266"]Item[/TD]
[TD="class: xl67, width: 98"]Mint Year[/TD]
[TD="class: xl67, width: 69"]Type[/TD]
[TD="class: xl67, width: 76"]Metal[/TD]
[TD="class: xl67, width: 52"]Oz[/TD]
[/TR]
</tbody>[/TABLE]

The entries have 3 different Types: Coin, Ingot and Round. I want a formula that will query the entire table and show me how many coins I have. How can I do this? I'm wanting to show the results on another sheet.

 

[sheetspread]

countif formula or pivot table or subtotaler

 

[Outdoorsman80]

Thank you for pointing me into the right direction, I was able to use countIf to could the amount of entries that were Silver.

=COUNTIF(E2:E2000,"Silver")

Now, what I need to do is calculate the weight of the silver items, I am stuck with that query. Here's what I tried, along with other formulas that got me no where:

=SUM(F2:F2000) * J3

I must have to have some sort of countif in there but am not sure.... Here's an example of the table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Date Purchased[/TD]
[TD]Item[/TD]
[TD]Mint year[/TD]
[TD]Type[/TD]
[TD]Metal[/TD]
[TD]Oz[/TD]
[/TR]
[TR]
[TD]12/1/16[/TD]
[TD]Silver Dollar[/TD]
[TD]2016[/TD]
[TD]Coin[/TD]
[TD]Silver[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12/2/16[/TD]
[TD]10oz Cooper Bar[/TD]
[TD]NA[/TD]
[TD]Ingot[/TD]
[TD]Copper[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]12/3/16[/TD]
[TD]2oz Gold Bar[/TD]
[TD]NA[/TD]
[TD]Ingot[/TD]
[TD]Gold[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12/4/16[/TD]
[TD]Silver Dollar[/TD]
[TD]2017[/TD]
[TD]Coin[/TD]
[TD]Silver[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Just like the COUNTIF function, there is also a SUMIF function.
It would look like this:

=SUMIF(E2:E2000,"Silver",F2:F2000)*J3

See here for help on SUMIF: https://www.techonthenet.com/excel/formulas/sumif.php

 

[Outdoorsman80]

Thank you, that makes sense now. If this were SQL I'd have a lot easier time. I think this will point me into the right direction.

 

[Joe4]

You are welcome!

 

[sheetspread]

If this were SQL I'd have a lot easier time.

You can write SQL queries in Excel or BI:

http://analystcave.com/create-microsoft-query-excel-excel-query/