CODEWITHSUNDEEP
excelexcel-formula
Chathura Uduwawala
Chathura Uduwawala

Reputation: 21

how to get sum of cells in a excel row based on the status mention in each of the near by cell

This is the sample data set I have: Sample

I am currently facing challenges in creating a formula in Excel to sum the values in odd-numbered columns based on the payment status in adjacent even-numbered columns.
My dataset starts at cell A3, where odd-numbered columns contain values, and even-numbered columns contain corresponding payment statuses.

I have attempted several formulas, including the use of SUMPRODUCT, but I am unable to achieve the desired result.

Here is the latest formula I've tried for "PAID" in cell T3:

=SUMPRODUCT((MOD(COLUMN(B3:S3)-COLUMN(B3), 2) = 0)*(B3:S3="PAID")*(A3:S3))

And for "NOTPAID" in cell U3:

=SUMPRODUCT((MOD(COLUMN(B3:S3)-COLUMN(B3), 2) = 0)*(B3:S3="NOTPAID")*(A3:S3))

Unfortunately, this formula is not producing the expected results. I suspect there might be an issue with the logic or the approach.

Could someone please provide guidance on creating a formula that correctly sums the values in odd-numbered columns based on the payment status in even-numbered columns?

Any help or suggestions would be greatly appreciated.

Upvotes: 0

Views: 180

Answers (2)

VBasic2008
VBasic2008

Reputation: 54815

Sum Adjacent to Matches

enter image description here

=LET(data,$B3:$S12,match_data,"PAID",
BYROW(data,LAMBDA(r,SUM(FILTER(r,HSTACK(
    DROP(IFNA(XMATCH(r,match_data),),,1),0),0)))))

Note that match_data could be T2 or U2 or T2:U2 or {"PAID";"NOT"} ... etc.

Edit: Another (Single Filter) Idea

=LET(data,B3:S12,group_cols,2,sum_col,1,filter_col,2,filter_string,"PAID",
    zs,group_cols*(SEQUENCE(COLUMNS(data)/group_cols)-1),
    t,CHOOSECOLS(data,zs+sum_col)*(CHOOSECOLS(data,zs+filter_col)=filter_string),
BYROW(t,LAMBDA(r,SUM(r))))

4 Columns, Single Filter

=LET(data,B3:Q12,group_cols,4,sum_col,1,filter_col,4,filter_string,{"PAID"},
    zs,group_cols*(SEQUENCE(COLUMNS(data)/group_cols)-1),
    t,CHOOSECOLS(data,zs+sum_col)*(CHOOSECOLS(data,zs+filter_col)=filter_string),
BYROW(t,LAMBDA(r,SUM(r))))

enter image description here

Upvotes: 0

Black cat
Black cat

Reputation: 6192

The formula main problem when you multiply a string with a boolean which is a value error. To resolve this use IFERROR sg. like this.

=SUM(IFERROR((B3:S3="paid")*A3:S3,0))

this also resolve the pair-impair column problem.

Sidenote: T column is your data last column. Therefore seems you need in U3 and V3

=SUM(IFERROR((B3:T3="paid")*A3:S3,0))

=SUM(IFERROR((B3:T3="notpaid")*A3:S3,0))

Upvotes: 0

Related Questions