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