SUM multiple Rows with INDEX / MATCH

[spinna2go]

Hi,

I have a spreadsheet where i want to check a column for multiple instances of a phrase and then sum the quantities in the adjoining column. The Phrase that appears in Column B is is chosen from a drop down list based on a selection made in Column A. All phrases are found in a table hosted in another sheet called Data1, and for now cell B2 holds the phase in question

Assume top left Cell is A1 and the Sheet name is Data

Type	Phrase	Quantity
Layer 3 Switch	48 Port PoE	5
Layer 3 Switch	48 Port PoE	6

<tbody>
</tbody>

In another sheet (Materials), I'm able to use a single INDEX / MATCH statement that that returns the Quantity to the appropriate cell. What i have been able to achieve is including that INDEX / MATCH. This INDEX Match formula is below.

=INDEX('Data'!$C$2:$C$3,MATCH(Data1!$B$2,'Data'!$B$2:$B$3,0))

This single formula picks up row 2 and returns the value 5.

I'm asking for your expertise to be able to SUM all rows that contain the phrase I'm referencing from Data1 in cell B2 (in this example "48 Port PoE")

Cheers
Darren

 

[Aladin Akyurek]

Materials

A2:

=SUMIFS(Data!C:C,Data!B:B,B2)

B2:

=Data1!B2

 

[spinna2go]

thanks.. it all looks so simple now!

 

[Aladin Akyurek]

thanks.. it all looks so simple now!

But it is!... By the way, you are welcome.

 

[spinna2go]

furthermore, can you nest the SUMIFS? the cell where i need the total value needs to reference a few phrases? So, in Data1 i need to reference cell B2 and C2 and combine the totals from the DATA sheet.

 

[Aladin Akyurek]

furthermore, can you nest the SUMIFS? the cell where i need the total value needs to reference a few phrases? So, in Data1 i need to reference cell B2 and C2 and combine the totals from the DATA sheet.

Try...

=SUMPRODUCT(SUMIFS(Data!C:C,Data!B:B,B2:C2))

 

[spinna2go]

thankyou thankyou thankyou!!

 

[Aladin Akyurek]

thankyou thankyou thankyou!!

You are welcome.