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

Krystal Garcia

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")))

Carson Penticuff

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")

Carson Penticuff

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")

Krystal Garcia

Thanks Carson. I figured it out with your help!

Krystal Garcia

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)

JamesB

@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})

Krystal Garcia

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.

Krystal Garcia

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")