Multiple Creitera

[josros60]

Hi,

what formula can I use for the data below, wan to find Sage ID and Account reference and get the amount $ for it.

here is the data:

[TABLE="width: 334"]
<tbody>[TR]
[TD]SAGE
ID[/TD]
[TD]Type[/TD]
[TD]Account
Reference[/TD]
[TD]Gross[/TD]
[/TR]
[TR]
[TD]100016[/TD]
[TD]NorthwesTel[/TD]
[TD]8327204[/TD]
[TD="align: right"]5794.25[/TD]
[/TR]
[TR]
[TD]100016[/TD]
[TD]NorthwesTel[/TD]
[TD]8327205[/TD]
[TD="align: right"]7705.03[/TD]
[/TR]
[TR]
[TD]100016[/TD]
[TD]NorthwesTel[/TD]
[TD]8335419[/TD]
[TD="align: right"]3435.22[/TD]
[/TR]
[TR]
[TD]100016[/TD]
[TD]NorthwesTel[/TD]
[TD]8092[/TD]
[TD="align: right"]750.07[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[Eric W]

If the Sage ID/Account Reference combination is unique, then this will work:

Book1
	A	B	C	D	E	F	G	H
1	SAGE	Type	Account	Gross		Sage ID	Account Ref	Gross
2	ID		Reference			100016	8335419	3435.22
3	100016	NorthwesTel	8327204	5794.25
4	100016	NorthwesTel	8327205	7705.03
5	100016	NorthwesTel	8335419	3435.22
6	100016	NorthwesTel	8092	750.07
Sheet4

Cell Formulas
Range	Formula
H2	=SUMIFS(D3:D6,A3:A6,F2,C3:C6,G2)

 

[josros60]

Hi,

Tried but give me an error #VALUE !

Formula:

=SUMIFS(AUGUST[Aug],AUGUST[SAGEID],A3,C3,AUGUST[AccountNumber])

thanks,

 

[Eric W]

You've got the parameters in the wrong order somehow. If A3 has the SageId, and C3 has the account number, and the Aug column has the totals, then it should be:

=SUMIFS(AUGUST[Aug],AUGUST[SAGEID],A3,AUGUST[AccountNumber],C3)


It seems a bit odd to have a column named Aug in a table named AUGUST. Do you mean:

=SUMIFS(AUGUST[Gross],AUGUST[SAGEID],A3,AUGUST[AccountNumber],C3)


https://support.office.com/en-us/article/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b

 

[josros60]

Sorry,

the column is not named August is the tab (sheet) have 12 tabs with each month.

 

[Eric W]

Now I'm confused. In your post 3, you showed a formula using table syntax, so I assumed you had a table named August, with column headings Aug, SAGEID, and AccountNumber. Now you say the sheet is named August? In order to refer to data on another sheet, the format is:

=SUMIFS(August!D3:D6,August!A3:A6,A3,August!C3:C6,C3)

where the ranges with sheet names go to another sheet, and the ones without the sheet names belong on the current sheet (the sheet with the formula). So in order to get your formula to work, I need to know where the lookup data is, and where the values are that you're looking for. Are they in a table? In a fixed range? An open-ended range? A defined Name? Do you want to just search the August sheet, or all 12 sheets?

 

[josros60]

This what I have

12 sheets with all vendors

each month has for example

Jun and July column to compare two month and COGS (cost of good gross) what I want when type the Month data automatically goes to to COGS Tab.

This is summarty tab (COGS)

A
[TABLE="width: 1102"]
<colgroup><col><col><col><col span="2"><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]SAGE
ID[/TD]
[TD]Type[/TD]
[TD]Account
Reference[/TD]
[TD] [/TD]
[TD]Date[/TD]
[TD]Mail/Online/Email[/TD]
[TD]GL/DIST CODE[/TD]
[TD]Notes[/TD]
[TD]$ Gross[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[/TR]
[TR]
[TD]100215[/TD]
[TD]ABC Communications[/TD]
[TD]17786-28380[/TD]
[TD] [/TD]
[TD]1ST[/TD]
[TD]Mail [/TD]
[TD]TOWLEA[/TD]
[TD] [/TD]
[TD="align: right"]1,344.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]525.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]100215[/TD]
[TD]ABC Communications[/TD]
[TD]17786-28620[/TD]
[TD] [/TD]
[TD]1ST[/TD]
[TD]Mail [/TD]
[TD]TOWLEA[/TD]
[TD] [/TD]
[TD="align: right"]1,044.75[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]100215[/TD]
[TD]ABC Communications[/TD]
[TD]30862[/TD]
[TD] [/TD]
[TD]1ST[/TD]
[TD]Mail [/TD]
[TD]TOWLEA[/TD]
[TD] [/TD]
[TD="align: right"]414.75[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]655.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]100109[/TD]
[TD]AFX Cpmmunications[/TD]
[TD]80017[/TD]
[TD] [/TD]
[TD]1ST[/TD]
[TD]Mail [/TD]
[TD]CLESER[/TD]
[TD] [/TD]
[TD="align: right"]152.69[/TD]
[TD="align: right"]115.89[/TD]
[TD="align: right"]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]100053[/TD]
[TD]Allstream[/TD]
[TD]1284630[/TD]
[TD] [/TD]
[TD]1st[/TD]
[TD]Online[/TD]
[TD]ALL463[/TD]
[TD] [/TD]
[TD="align: right"]4,644.06[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]100053[/TD]
[TD]Allstream[/TD]
[TD]10000164982[/TD]
[TD] [/TD]
[TD]1ST[/TD]
[TD]Online[/TD]
[TD]ALL498[/TD]
[TD] [/TD]
[TD="align: right"]699.45[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


and here is the month tab sample:

[TABLE="width: 660"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]SAGE
ID[/TD]
[TD="align: left"]

Vendor Name

<tbody>
</tbody>

[/TD]
[TD]Account
Number[/TD]
[TD] Jul [/TD]
[TD] Aug [/TD]
[/TR]
[TR]
[TD]100210[/TD]
[TD]0875942 BC Limited[/TD]
[TD][/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100306[/TD]
[TD]A2B Delivery Service[/TD]
[TD][/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100215[/TD]
[TD]ABC Communications[/TD]
[TD]17786-28380[/TD]
[TD] - [/TD]
[TD] 525.00[/TD]
[/TR]
[TR]
[TD]100215[/TD]
[TD]ABC Communications[/TD]
[TD]17786-28620[/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100215[/TD]
[TD]ABC Communications[/TD]
[TD]30862[/TD]
[TD] - [/TD]
[TD] 655.00[/TD]
[/TR]
[TR]
[TD]100128[/TD]
[TD]Abstractive Technologies Consulting[/TD]
[TD][/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100164[/TD]
[TD]ADP CANADA CO.[/TD]
[TD][/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100037[/TD]
[TD]AEBC Internet Corp. (AE170403-6455)[/TD]
[TD][/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100109[/TD]
[TD]AFX Communications[/TD]
[TD][/TD]
[TD] 115.89[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]100176[/TD]
[TD]AIRESPRING[/TD]
[TD]1352791[/TD]
[TD] - [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

want to get the amount I type month tab to go to COGS TABLE

 

[Eric W]

OK, we're making some progress. On this example, you have July and August on the same sheet. So your JULY sheet has July and August? Your AUGUST sheet has August and September? So on your COGS sheet, do you want to take the August value from the JULY sheet or the AUGUST sheet?

 

[josros60]

Yes, all same sheet and each tab (month) has two month comparison, ej. Jan will Have Dec and Jan, Feb will have Jan and Feb and the Summary called COGS (COST OF GOODS).
and want to take the total of the each tab name, example July tab will have Jun column and July column and COGS will take july from july tab respectively the rest of tabs.

 

[Eric W]

Assuming your July sheet looks like:

Book1
	A	B	C	D	E
1	SAGE ID	Vendor Name	Account Number	Jul	Aug
2	100210	0875942 BC Limited		-
3	100306	A2B Delivery Service		-
4	100215	ABC Communications	17786-28380	-	525
5	100215	ABC Communications	17786-28620	-
6	100215	ABC Communications	30862	-	655
7	100128	Abstractive Technologies Consulting		-
8	100164	ADP CANADA CO.		-
9	100037	AEBC Internet Corp. (AE170403-6455)		-
10	100109	AFX Communications	80017	115.89
11	100176	AIRESPRING	1352791	-
July

And August looks like:

Book1
	A	B	C	D	E
1	SAGE ID	Vendor Name	Account Number	Aug	Sep
2	100210	0875942 BC Limited
3	100306	A2B Delivery Service
4	100215	ABC Communications	17786-28380	525
5	100215	ABC Communications	17786-28620
6	100215	ABC Communications	30862	655
7	100128	Abstractive Technologies Consulting
8	100164	ADP CANADA CO.			888
9	100037	AEBC Internet Corp. (AE170403-6455)			999
10	100109	AFX Communications
11	100176	AIRESPRING	1352791
August

Then on your COGS sheet you can use the following formula:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	SAGE ID	Type	Account Reference		Date	Mail/Online/Email	GL/DIST CODE	Notes	$ Gross	19-Jul	19-Aug	19-Sep
2	100215	ABC Communications	17786-28380		1ST	Mail	TOWLEA		1,344.00	0	525	0
3	100215	ABC Communications	17786-28620		1ST	Mail	TOWLEA		1,044.75	0	0	0
4	100215	ABC Communications	30862		1ST	Mail	TOWLEA		414.75	0	655	0
5	100109	AFX Cpmmunications	80017		1ST	Mail	CLESER		152.69	115.9	0	0
6	100053	Allstream	1284630		1st	Online	ALL463		4,644.06	0	0	0
7	100053	Allstream	10000164982		1ST	Online	ALL498		699.45	0	0	0
COGS

Cell Formulas
Range	Formula
J2	=SUMIFS(INDIRECT(TEXT(J$1,"mmmm")&"!D2:D100"),INDIRECT(TEXT(J$1,"mmmm")&"!A2:A100"),$A2,INDIRECT(TEXT(J$1,"mmmm")&"!C2:C100"),$C2)

Put it in J2, and copy it down and to the right as needed. Note that it assumes the the dates in J1:L1 are actual Excel dates.