Sumifs?

[Pripri]

Hi, I'm trying to create a formula that will give me a monthly total paid to a vendor from my check register. My data is displayed in Fiscal Year (FY) Check date, Vendor Name, Transaction Amount Period, Expense Category(Frog_Description) and Program name. My first challenge is with the last month on my FY. On any FY, my September month will be called period 12 and period 13. To find the total paid by a vendor on September, I will need to add any checks paid for the vendor in both periods (12 and 13). I also pay the same vendor through different programs, my formula needs to be specific by a combination of a specific vendor, a specific program and a specific expense category. Every month I'm adding the new checks cut for that month in the bottom of my excel (first tab), so ideally I wouldn't like to have the formula blocked $ to a specific cel
On my formula, I'm trying to do a Sumifs, but I'm not getting anywhere. I also tried Sumproduct.
This is the formula I created with zero return
=SUMIFS('Check Register'!$D:$D,'Check Register'!$A:$A,'Expenses Detail Formulas'!D$1,'Check Register'!$C:$C,'Expenses Detail Formulas'!$C11,'Check Register'!$E:$E,'Expenses Detail Formulas'!D$2,'Check Register'!$F:$F,'Expenses Detail Formulas'!$B11,'Check Register'!$G:$G,'Expenses Detail Formulas'!$A11)

I highlighted in a peach color the results I'm expecting. The first tab on my spreadsheet is the check register and the second one is where I would like to get my results. I need a better formula, or a correction to mine that will give me the total of 3,995 when looking at Vendor Harden & Associates Inc for Administration Program, Expense Cat. Purchased Services for the month of October which is period 1 in the FY 19. Please do not forget about the checks that will be either in period 12 or 13 but needs to be added into one. Thank you! I hope I'm not confusing anyone.

Book1
	A	B	C	D	E	F	G
1	FY	Check Date	Vendor Name	Transaction Amount	Period	Expense Category	Program Name
2	19	7/18/2019	FIDUCIARYVEST LLC	12,500.00	10	PURCHASED SERVICES	Administration
3	19	7/18/2019	FIDUCIARYVEST LLC	11,250.00	10	PURCHASED SERVICES	Administration
4	19	10/18/2018	HARDEN & ASSOCIATES INC	3,995.00	1	PURCHASED SERVICES	Administration
5	19	12/13/2018	HARDEN & ASSOCIATES INC	30,000.00	3	PURCHASED SERVICES	Administration
6	19	12/13/2018	HARDEN & ASSOCIATES INC	30,000.00	3	PURCHASED SERVICES	Administration
Check

Book1
	A	B	C	D	E	F	G	H
1				19	19	19	19	19
2				1	2	3	4	5
3	Program Name	Frog_Description	Vendor Name	10/31/2018	11/30/2018	12/31/2018	1/31/2019	2/28/2019
4	Administration	PURCHASED SERVICES	AON RISK SERVICES INC OF FLORIDA			140,000	10,600
5	Administration	PURCHASED SERVICES	ARC ADVISORS LLC	7,452	6,473	5,281	11,717	12,264
6	Administration	PURCHASED SERVICES	CDW LLC			32,867	14,928	5,920
7	Administration	PURCHASED SERVICES	CROWE HEALTHCARE RISK CONSULTING		80,000		120,000	41,200
8	Administration	PURCHASED SERVICES	DRAFFIN & TUCKER LLP
9	Administration	PURCHASED SERVICES	FIDUCIARYVEST LLC				11,250
10	Administration	PURCHASED SERVICES	GOLDEN HOUR DATA SYSTEMS INC			26,702	30,522	73,596
11	Administration	PURCHASED SERVICES	HARDEN & ASSOCIATES INC	3,995		60,000
12	Administration	PURCHASED SERVICES	HIMFORMATICS LLC
13	Administration	PURCHASED SERVICES	IMMEDIACY PUBLIC RELATIONS INC	4,500	4,500	4,500	4,500	4,500
14	Administration	PURCHASED SERVICES	IRON MOUNTAIN INC				16,770	5,663
15	Administration	PURCHASED SERVICES	LOGICALIS INC		15,238	15,238	30,477
Expenses Detail Formulas

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:B3	Cell Value	contains "FCP Division"	text	NO
C3	Cell Value	contains "FCP Division"	text	NO

 

[jasonb75]

It looks as if the problem is not with the formula.

The cells in column B of the formula sheet contain

"PURCHASED SERVICES       "

while those in column F of the check sheet contain

"PURCHASED SERVICES"

The extra spaces in the formula sheet mean that they are not a match. You could work around it by changing the criteria to TRIM(B11) but it would be better to have a clean source to begin with.

 

[Pripri]

It looks as if the problem is not with the formula.

The cells in column B of the formula sheet contain

"PURCHASED SERVICES       "

while those in column F of the check sheet contain

"PURCHASED SERVICES"

The extra spaces in the formula sheet mean that they are not a match. You could work around it by changing the criteria to TRIM(B11) but it would be better to have a clean source to begin with.

Unbelievable! Thank you so much! I used the trim formula to correct the problem.