Sumifs 2 columns
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")
Best Answer

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
Answers

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)

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

=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

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

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

Here are 3 questions to please confirm:
 The Ret Dep column is what you want summed
 The formula was working, as expected, prior to the addition of the new column: NonCash
 NonCash is a date column
A screenshot of the Payment Log sheet would be helpful (no sensitive data)

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

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)

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.

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

Second one work! Thank you so much for your time. I really appreciate it.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!