# How do I create a IF(AND formula combining multiple cross references?

Options
✭✭✭✭✭

In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula:

=COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback Intake Sheet Type of CB}, "AMEX")))

• ✭✭✭✭✭✭
Options

I think I am following what you are attempting to do. Try this:

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, "AMEX")

• ✭✭✭✭✭✭
Options

I think I am following what you are attempting to do. Try this:

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, "AMEX")

• ✭✭✭✭✭
edited 08/11/23
Options

Thanks Carson. I figured it out with your help!

• ✭✭✭✭✭
Options

I need some more help here, Now trying to find the Sum dollar amount (instead of just line item count). What is the SUMIFS formula? I am trying to pull the total dollar amount being disputed for each branch if the chargeback type is "AMEX". Here is what I am using but it says #Incorrect argument set:

=SUMIFS({Dispute & Chargeback intake sheet branch}, \$[Data Points]@row, {Dispute & Chargeback Intake Sheet Type of CB}, "AMEX", {Dispute&CB intake sheet Ttl disputed/cb amt})

(I have 3 different references in this)

• ✭✭✭✭✭✭
edited 08/18/23
Options

@Krystal Garcia The frist part of a SUMIFS is where to get the numbers to SUM. Based on your picture your SUMIFS should start with the column name that has the AMEX CB amounts. The rest of the arguments in a SUMIFS are the requirements for when to pull an amount from the AMEX CB column.

=SUMIFS([Total AMEX CB \$ RO Amount]:[Total AMEX CB \$ RO Amount],{Dispute & Chargeback intake sheet branch}, \$[Data Points]@row, {Dispute & Chargeback Intake Sheet Type of CB}, "AMEX", {Dispute&CB intake sheet Ttl disputed/cb amt})

• ✭✭✭✭✭
Options

Thanks for your help JamesB! So there are no amounts to start with in the Total Amex CB Amounts columns. I am trying to bring in that data to that column from other sheets.

• ✭✭✭✭✭
Options

I figured it out though. I think I just needed to get out of that for a while. Here is the correct formula:

=SUMIFS({Dispute&CB intake sheet Ttl disputed/cb amt}, {Dispute & Chargeback intake sheet branch}, \$[Data Points]@row, {Dispute & Chargeback Intake Sheet Type of CB}, "Amex")

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!