CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Jay
Jay

Reputation: 666

Google Sheet Formula SUM values of all cells based on ROW and COLUMN

In Google Sheets, what would be the best (Possibly complex) formula to get the SUM of all cells in a table IF the value on column header (Row 1 of each cells) contains a specific word and the value of the 1st column contains an exact word?

Product ID New York Florida Sub Washington Arizona Florida Main Washington
PID-001 50 20 36 50 99 36
PID-002 87 78 72 36 44 55
PID-003 44 55 64 99 20 50
PID-004 52 10 36 24 87 87

Sample Spreadsheet

Sample sheet above. Basically I wanted to get the total of all quantities for the Product ID PID-003 if the store name has the word "Florida". In the sample sheet the total should be 75.

I have tried complex formulas same as below but either I get an error or I get blank.

=SUM(FILTER($A:$G,REGEXMATCH($A1:$G1,"Florida"),REGEXMATCH($A1:$A5,"PID-003")))

After a day of working I just had to seek professional assistance. Note that this sample is a small data, and actual data is large.

Upvotes: 1

Views: 63

Answers (4)

doubleunary
doubleunary

Reputation: 19145

The formula in the question assumes that filter() can process horizontal and vertical criteria in one go, when it can only do one or the other. To make it work, add another filter(), like this:

=sum(ifna(filter( 
  filter(B2:G, search("Florida", B1:G1)), 
  A2:A = "PID-003"
)))

...or, unprettified:

=sum(ifna(filter(filter(B2:G,search("Florida",B1:G1)),A2:A="PID-003")))

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34355

You can also use Xlookup to get a row of the 2d range, then sumif or sumifs with wildcards:

=sumifs(xlookup(I2,A2:A5,B2:G5),B1:G1,"*"&J1&"*")

where J1 contains "Florida" and I2 contains "PID-003"

Upvotes: 1

PatrickdC
PatrickdC

Reputation: 2486

Use SUMPRODUCT

If you are set to use the SUM function, you may try to use:

=ARRAYFORMULA(SUM((A2:A5="PID-003")*(IFERROR(SEARCH("Florida", B1:G1)))*(B2:G5)))

Wherein the product of the conditions will result in multiplying the needed data by 1 and the unwanted data by 0 (with the help of IFERROR and SEARCH). However, this is already the sum of products logic so I recommend using the SUMPRODUCT function. The new formula should look like:

=SUMPRODUCT((A2:A5="PID-003")*(IFERROR(SEARCH("Florida", B1:G1)))*(B2:G5))

References:

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37050

You need nested FILTER() then sum. Try-

=SUM(FILTER(FILTER(B2:G5,INDEX(REGEXMATCH(B1:G1,"Florida"))),A2:A5="PID-003"))

Upvotes: 2

Related Questions