Excel IFS function 2 sheets, multiple columns, just a yes/no result.

Question

I have a workbook that i need to auto-populate if a payment was made during a certain month on one sheet from the other. 

Sheet 1 is like a registry i enter receipts into.

Sheet 2 is the summary of everything (Hopefully).

I would like to be able to see if a payment was made on Sheet 1.

Sheet1 

These columns must be true:

ColumnA=Year (This has a formula to determine year from the date i enter on column C)

ColumnB=Month (This has a formula to determine month from the date i enter on column C)

ColumnG=Name

ColumnM=Payment (This is a drop down list column, it has "Ordered" or "Payment" for types)

So for Jane Doe on July 2018 was a payment made?

Do I use IF, IFS, AND?

Thank you, 

Eppab

Answer 1

=IF(SUMPRODUCT(--(Lisa Barcon='2018 Accrued Paid Fines & Fees'!G3:G212364)*(2018='2018 Accrued Paid Fines & Fees'!A3:A212364)*(7='2018 Accrued Paid Fines & Fees'!B3:B212364)*(Payment='2018 Accrued Paid Fines & Fees'!M3:M212364))=1,"Yes","No")

This is the formula i put in July for the first name on my list. My Sheet1 is called 2018 Accrued Paid Fines & Fees. 

I plugged in her name of sheet 1 and entered a payment for July 2018, just to make sure she has 1. 

My result is #NAME?

So, i am doing something wrong. Thank you for all your help!

Answer 2

Hi Eppab

Well I created range names and they can be scoped so they refer to the Workbook so just entering the name refers to both the range and its parent sheet.

If you have not gone that way you must include the sheet in the range.  If Name refers to Sheet1 D2:D400 the your formula in sheet 2   will be =Sheet1!D2:D400.  The easieast way is to type = then point to the range in sheet 1 and Excel will fill in the reference for you.

Answer 3

Sheet 1-Registry

Sheet 2, Summary

Answer 4

I will try. Will it work with getting data from Sheet1 onto Sheet2?

Answer 5

So for Jane Doe on July 2018 was a payment made?

Do I use IF, IFS, AND?

Here is one way with IF & SUMPRODUCT

=IF(SUMPRODUCT(--(Name=H2)*(Year=I2)*(Month=J2)*(Payment=Payment))=1,"Payment","Not Paid")