Formula based on Cell content

[Outdoorsman80]

I have a spread sheet with several columns:

Date Purchased

Item

Mint Year

Type

Metal

Oz

<tbody>
</tbody>

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:

A	B	C	D	E	F
Date Purchased	Item	Mint year	Type	Metal	Oz
12/1/16	Silver Dollar	2016	Coin	Silver	1
12/2/16	10oz Cooper Bar	NA	Ingot	Copper	10
12/3/16	2oz Gold Bar	NA	Ingot	Gold	2
12/4/16	Silver Dollar	2017	Coin	Silver	1

<tbody>
</tbody>

 

[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/