Sumifs 2 columns

ginamt3

Hi, I need to add a column to my sumifs formula. I need to check if there is a payment in a new columns

This is my current formula.

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel")

Kelly Moore

Are you wanting to add together the Sums from your Column Retains and Non Cashes together to get one total?

When you wrote this:

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel", {Payment Log 2022 NonCash}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel")

Did you mean

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel") + SUMIFS( {Payment Log 2022 NonCash}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel")

Try this and see if it is what you expected

Kelly

Kelly Moore

Hey @ginamt3

What will indicate that a payment has been made? A Date? A dollar amount above zero? A checkmark?, etc. If you tell me what indicates a payment and tell me the name of your new range (column) I'll show you exactly what the formula will look like.

To the end of your existing formula (before the closing parenthesis), you will add a comma, the new column, a comma, then the criteria for the indicated payment)

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel", {new range}, criteria for payment)

ginamt3

same criteria as the first {Pay Log RETAIN} it's based on dates.

ginamt3

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel", {Payment Log 2022 NonCash}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel")

This produces an error

Kelly Moore

Hey Gina

Remember you must keep the syntax of one range and one critieria pair. I'm not sure why many of the criteria are repeated? Inadvertent copy paste? I deleted the repeated terms.

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel", {Payment Log 2022 NonCash}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022))

Does the above give you what you needed?

Kelly

ginamt3

I just tested. I put an amount in the NonCash and it did not update the column with the amount. no error given

Kelly Moore

Here are 3 questions to please confirm:

1. The Ret Dep column is what you want summed
2. The formula was working, as expected, prior to the addition of the new column: NonCash
3. NonCash is a date column

A screenshot of the Payment Log sheet would be helpful (no sensitive data)

ginamt3

1. The Ret Dep column is what you want summed Yes, for the Week and specific PM
2. The formula was working, as expected, prior to the addition of the new column: NonCash, Yes
3. NonCash is a date column no, it's currency

Screen Shot 2022-09-27 at 11.43.17 AM.png

Kelly Moore

Hey

So NonCash not being a date column our problem with the current formula since the criteria is referring to dates. What is it about the NonCash column that indicates a payment has been made? A value greater than zero? I added this criteria in case I guessed correctly. If not, let me know and we can tweak. In your screenshot the NonCash column only shows blank cells.

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel", {Payment Log 2022 NonCash}, >0)

ginamt3

Maybe this will help. So in the Dean column for this week 9/26/22 it's supposed to look at the Column Retain/Deposit and NonCash and calculate the sum of payments for that week.

Does that make sense? the original formula just did it for the Retain/Deposit week. Now I need to add the noncash.

Kelly Moore

Are you wanting to add together the Sums from your Column Retains and Non Cashes together to get one total?

When you wrote this:

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel", {Payment Log 2022 NonCash}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel")

Did you mean

=SUMIFS({payment Log Ret Dep}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel") + SUMIFS( {Payment Log 2022 NonCash}, {Pay Log RETAIN}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = Week@row, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2022), {Payment Log PM2}, "Dean Hammel")

Try this and see if it is what you expected

Kelly

ginamt3

Second one work! Thank you so much for your time. I really appreciate it.