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 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.
You need nested FILTER() then sum. Try-
=SUM(FILTER(FILTER(B2:G5,INDEX(REGEXMATCH(B1:G1,"Florida"))),A2:A5="PID-003"))