Calculate value from one row based on the text value of another row

Row A of products, Product A, Product B.....Product F, over 100 rows.
Row B has cost of the products in Row A. Product A has three different prices throughout the row and same with other products.

What I want to do:
ROW D auto calculate
Product A
Product B
Product C
...
Product F

Row E auto calculate
Total cost of Product A
Total cost of Product B
and so on...

In the future if I add Product G, I want that to show up in ROW D and Total of it in Row E.

Can anyone help?

Capture.JPG

Thank you.

Thank you for you help. AMAZING and super fast reply.

I am having a problem. I have row a and b merged. it didn't work when two rows are merged. When I un-merged the rows, product a and b show up. next row is blank, nothing shows up after that. when i enter something in the black row everything appears.

Originally Posted by azumi

For Products, put this on column D and copied down:
=IFERROR(INDEX($A$3:$A$500,MATCH(0,INDEX(COUNTIF($D$2:D2,$A$3:$A$500&""),0,0),0)),"")

For Totals, put this on column E and copied down
=SUMIFS($B$3:$B$500,$A$3:$A$500,$D3)

1.JPG

2.JPG

Originally Posted by azumi

PLease don't put an image, upload your excel file to the forum, click on "Go Advanced" button, then scoll down to find "Manage Attachments" text button to attach your file


regards
Azumi

Sorry about that.

attached is the example file

also can anything be done if row A and B are merged?

thanks

Ran into another problem with this.

If I enter the cost. the same cost goes in all available slots.

Capture.JPG

Originally Posted by GeoffW283

Thanks for the feedback. Azumi did all the hard work here

One more addition to the same string.

I want to create a new row that shows beside product column, how many line items were there for each product.

In this case C3 Would show total of D3 (A) how many time Product A appeared in Column A.

Total product A... So on

Dear Na-Dude : your post 12# file not open. Can you re attach valid file.

In "C3"

Formula:

Please Login or Register  to view this content.

This will only do product "A"? I want it to count all products? Do I just manually change for the 100 items? That would take a long time. Any work around to auto do for all rows, A to Z...also need to keep in mind there are empty rows to account for.

Thanks

Kindly change range 499 to actual range as 19 in "D" & "E" columns.
See attachment.

So I used the same advise from GeoffW283 and added =IF(D2="","", in front of the countif statement. Its appeared to have worked.

=IF(D2="","",COUNTIF($A$2:$A$500,$D2))

Capture.JPG