google sheets find by row and sum

user_3493113 0
Viewed 155

This is a simplified version of my data.
enter image description here
I want to calculate the "Whole Tax" row Automatically. I mean I'm looking for any special google sheets formula that can search for those records that are equal to "TAX" and SUM on the values in corresponding month.

google-sheets
4 Answers

In google sheet QUERY() function is really faster and more scalable. You can use wildcard character to ignore leading and trailing spaces. Try below formula.

=SUM(QUERY($A$3:$D$16,"SELECT B WHERE LOWER(A) LIKE '%tax%'"))

On single go get sum of all columns. As per my screenshot put below formula to B17 cell.

=QUERY($A$3:$D$16,"SELECT SUM(B), SUM(C), SUM(D) WHERE LOWER(A) LIKE '%tax%' LABEL SUM(B) '', SUM(C) '', SUM(D) ''")

enter image description here

user_55147470

Consider placing sums at the top of each column so you can select cells using A2:A for rest of column.

Anyhow, use =SUMIF($A$1:$A$15, "*tax", B1:B15) for strings that end in tax, case-insensitive. The wildcard selectors are * for wildcard strings and ? for wildcard characters. Use ~ to escape.

user_165968200

You should have posted a spreadsheet instead of an image to find out your locale. Try in B17, assuming TAX is not preceded by white spaces

=SUMPRODUCT($A$2:$A$16="TAX",B2:B16)
user_150193800

Following on from Harun24HR, here is a more flexible formula in which you do not have to designate each column

=QUERY({A3:D16},"SELECT SUM(Col"&arrayformula(textjoin("),SUM(Col",,COLUMN(B3:D)))&") WHERE LOWER(Col1) LIKE '%tax%' LABEL SUM(Col"&arrayformula(textjoin(")'', SUM(Col",,COLUMN(B3:D)))&") '' ")
user_150193800
Related