Help with Sumifs formula

[willow1985]

I have the below formula for the example pic of data:

="Value Invoiced: "&DOLLAR(SUMIFS(WOR[[#All],[Amount]],WOR[[#All],[Customer]],"<>Theirs",WOR[[#All],[Date Invoiced]],">="&GraphData!$F$2,WOR[[#All],[Date Invoiced]],"<="&GraphData!$F$1),0)

(Note the table is called WOR)

I was wondering how I could add one more condition. I only want to sum unique invoice numbers (not all of the duplicates). As you can see Invoice #17125 and 15556 repeats. With this formula I would get a total of $113,488.45 when what I need is $7,313.76.

Any help would be much appreciated.

Thank you

Carla

 

[DanteAmor]

Try this array formula:

Dante Amor
	A	B	C	D	E	F	G	H
1	Customer	WorkOrder	Invoice No.	Amount	Date Invoiced	15-jun
2	Ours	206632	12345	1068	10-may	16-may		Value Invoiced: $7,289
3	Theirs	206633	15556	147	20-may
4	Theirs	206634	15556	147	20-may
5	Ours	206635	17125	6245	10-jun
6	Ours	206636	17125	6245	10-jun
7	Ours	206637	17125	6245	10-jun
8	Ours	206638	17125	6245	10-jun
9	Ours	206639	17125	6245	10-jun
10	Ours	206640	18888	1044	25-may
11	Ours	206641	18888	1044	25-may
12	Ours	206642	18888	1044	25-may
13	Ours	206643	29299	2000	18-jun
GraphData

Cell Formulas
Range	Formula
H2	H2	="Value Invoiced: " & DOLLAR(SUM(IF(FREQUENCY(IF((Tabla3[Customer]<>"Theirs")*(Tabla3[Date Invoiced]>=GraphData!$F$2)*(Tabla3[Date Invoiced]<=GraphData!$F$1),MATCH(Tabla3[Invoice No.],Tabla3[Invoice No.],0)),ROW(Tabla3[Invoice No.])-ROW($D$2)+1),Tabla3[Amount])),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

You can also use SUMPRODUCT so you don't need the array entry:

Book5
	A	B	C	D	E	F	G
1	Customer	WorkOrder	Invoice No.	Amount	Date Invoiced	15-Jun
2	Ours	206634	12345	1068.19	9-Jun	16-May
3	Theirs	206633	15556	147	7-Jun
4	Theirs	206632	15556	147	7-Jun		Value Invoiced: $7,314
5	Ours	206628	17125	6245.57	3-Jun
6	Ours	206627	17125	6245.57	3-Jun
7	Ours	206626	17125	6245.57	3-Jun
8	Ours	206625	17125	6245.57	3-Jun
9	Ours	206624	17125	6245.57	3-Jun
10	Ours	206623	17125	6245.57	3-Jun
11	Ours	206622	17125	6245.57	3-Jun
12	Ours	206621	17125	6245.57	3-Jun
13	Ours	206620	17125	6245.57	3-Jun
14	Ours	206619	17125	6245.57	3-Jun
15	Ours	206618	17125	6245.57	3-Jun
16	Ours	206616	17125	6245.57	3-Jun
17	Ours	206615	17125	6245.57	3-Jun
18	Ours	206614	17125	6245.57	3-Jun
19	Ours	206613	17125	6245.57	3-Jun
20	Ours	206612	17125	6245.57	3-Jun
21	Ours	206611	17125	6245.57	3-Jun
22	Ours	206610	17125	6245.57	3-Jun
23	Theirs	206608	14892	1044.75	12-Jun
GraphData

Cell Formulas
Range	Formula
G4	G4	="Value Invoiced: "&DOLLAR(SUMPRODUCT(WOR[Amount],--(WOR[Customer]<>"Theirs"),--(WOR[Date Invoiced]>=GraphData!$F$2),--(WOR[Date Invoiced]<=GraphData!$F$1),--(MATCH(WOR[Invoice No.],WOR[Invoice No.],0)=ROW(WOR[Invoice No.])-ROW(INDEX(WOR[Invoice No.],1))+1)),0)

 

[willow1985]

Both are giving me a invoice value of 0???

 

[willow1985]

Never mind. Figured out I was referencing the wrong page.

Thank you very much!

 

[willow1985]

Both work perfectly!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[Eric W]

Glad we could help!

 

[willow1985]

Try this array formula:

Dante Amor
	A	B	C	D	E	F	G	H
1	Customer	WorkOrder	Invoice No.	Amount	Date Invoiced	15-jun
2	Ours	206632	12345	1068	10-may	16-may		Value Invoiced: $7,289
3	Theirs	206633	15556	147	20-may
4	Theirs	206634	15556	147	20-may
5	Ours	206635	17125	6245	10-jun
6	Ours	206636	17125	6245	10-jun
7	Ours	206637	17125	6245	10-jun
8	Ours	206638	17125	6245	10-jun
9	Ours	206639	17125	6245	10-jun
10	Ours	206640	18888	1044	25-may
11	Ours	206641	18888	1044	25-may
12	Ours	206642	18888	1044	25-may
13	Ours	206643	29299	2000	18-jun
GraphData

Cell Formulas
Range	Formula
H2	H2	="Value Invoiced: " & DOLLAR(SUM(IF(FREQUENCY(IF((Tabla3[Customer]<>"Theirs")*(Tabla3[Date Invoiced]>=GraphData!$F$2)*(Tabla3[Date Invoiced]<=GraphData!$F$1),MATCH(Tabla3[Invoice No.],Tabla3[Invoice No.],0)),ROW(Tabla3[Invoice No.])-ROW($D$2)+1),Tabla3[Amount])),0)
Press CTRL+SHIFT+ENTER to enter array formulas.

I modified your formula a little bit but I cannot seem to get it to work and receive a #N/A error and am not sure why.

Could you advise what I am missing?

{="WIP Value: " & DOLLAR(SUM(IF(FREQUENCY(IF((WOR[Customer Exclusions]<>"Internal")*(WOR[Warehouse Name]="WIP"),MATCH(WOR[Q/O Number],WOR[Q/O Number],0)),ROW(WOR[Q/O Number])-ROW($N$2)+1),WOR[Sub Total])),0)}

 

[DanteAmor]

In column T you have blank cells.
Try this:

varios 17jun2020.xlsm
	E									N						T						Z	AA
1	Warehouse Name									Sub Total						Q/O Number						Customer Exclusions
2	WIP									150						14							WIP Value: $650
3	WIP									150						14
4	WIP									200						15						Internal
5	WIP									300						16
6	WIP									200						17
7	WIP									300						16
8	WIP
9	WIP									300						16
10	WIP
11	WIP
Hoja17

Cell Formulas
Range	Formula
AA2	AA2	="WIP Value: " & DOLLAR(SUM(IF(FREQUENCY(IF((WOR[Customer Exclusions]<>"Internal")*(WOR[Warehouse Name]="WIP")*(WOR[Q/O Number]<>""),MATCH(WOR[Q/O Number],WOR[Q/O Number],0)),ROW(WOR[Q/O Number])-ROW($N$2)+1),WOR[Sub Total])),0)
Press CTRL+SHIFT+ENTER to enter array formulas.