SUMIF Match Text Add Values

user_1154427 0
Viewed 123

I have a spreadsheet (in Google Sheets but would also like this to work in Excel if possible) that has a bunch of formulas using various ingredients. They are in the following format:

Spreadsheet snippet

How can I create a SUMIF formula that will scan this spreadsheet for a certain value, say "brown sugar" and add the value directly to the right?

I know how to do this on a particular column using the following formula:

=SUMIF(A1:A4,"*brown sugar*",B1:B4)

How can I do this for any column within the spreadsheet? Look for "brown sugar" and add the value to the right together with any other occurrences?

To take it a step further, how can I have a cell in the spreadsheet be my input field?

ingredient

Would like to be able to enter values using a wildcard and reference that cell (A2 in the below example) in the SUMIF

=SUMIF(A4:A7,A2,B4:B7)

Thank you in advance for any help on this!

google-sheets-formula google-sheets excel-formula excel
2 Answers

You can use following array formula:

=SUM(IFERROR(($A$1:$G$11=A15)*$B$1:$H$11,0))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

To search text with wildcards use:

=SUM(IFERROR((SEARCH(A15,$A$1:$G$11))*$B$1:$H$11,0))

For Google sheets use:

=ArrayFormula(SUM(IFERROR(($A$1:$G$11=A15)*$B$1:$H$11,0)))

=ArrayFormula(SUM(IFERROR((SEARCH(A15,$A$1:$G$11))*$B$1:$H$11,0)))
user_121888610

For both Excel and Google Sheets you can use

=SUMIF(A1:H11,A15,OFFSET(A1:H11,0,1))

enter image description here

Functions used:

user_15277800
Related