Sumproduct formula help

[Date636]

In the file there are 2 sheets. one is "Count Sheet" the other is "FMS" When I count every thing in the store I want to use my sheet for speed.

AS I fill out the Sheet "count Sheet" I need the "Total" from "Count Sheet" to be imputed in the "FMS" sheet in the correct spot.

EX. IN the "Count Sheet" there is a line for Med. Pan, I need the Med Pan Total imputed into the "FMS" sheet for the Med Pan Total.


This way I can inventory every section of the store into the "count Sheet" and this will help my speed .

The "FMS" actually has things list in a different order, so I am trying to avoid the hunting around for the items.

I hope to be able just look at the tab "FMS" the input all the totals into the computer at work. I will have that match the order of the items on the computer at work. As I have no control over the order their system puts the items in.


Thank you in advance

Becky

[AlKey]

Copy formula in B2 on your FMS sheet and drag it down.

=SUMPRODUCT(('Count Sheet'!$A$2:$A$10=A2)*('Count Sheet'!$B$2:$D$10))

[FDibbins]

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[Date636]

Thank you so much ALKey.


You saved me tons of time.

Becky

[AlKey]

You're Welcome and thank you for the feedback! Please change your thread title and mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved)

[Date636]

I Have modified the file. The formula does not work now. I can upload the new file in 4 days, as I am still finishing it. Then If you can work you magic again I would appreciate it.

I am still new to the forum , I am trying to change the name of the thread as the moderator stated, but seem to be having a problem doing it.

Thank you

Becky

[FDibbins]

If you will be having duplicate entries in COUNT, , in FMS A2, copied down, use this...
=IFERROR(INDEX('Count Sheet'!$A$2:$A$40,MATCH(0,INDEX(COUNTIF($A1:A$1,'Count Sheet'!$A$2:$A$40),0,0),0)),"")
If there will not be duplicate entries, then just use this instead...
=IF('Count Sheet'!A2="","",'Count Sheet'!A2)

Then in FMS B2, copied down, use this...
=IFERROR(INDEX('Count Sheet'!$A$2:$A$40,MATCH(0,INDEX(COUNTIF($A1:A$1,'Count Sheet'!$A$2:$A$40),0,0),0)),"")=IF(A2="","",INDEX('Count Sheet'!G:G,MATCH(FMS!A2,'Count Sheet'!A:A,0)))
=IF(A2="","",INDEX('Count Sheet'!G:G,MATCH(FMS!A2,'Count Sheet'!A:A,0)))