How to use SUMIFS in place of SUMPRODUCT?(2 Scenarios)

[xlmaniac]

Dear All,
I would like to use SUMIFS for below mentioned 2 scenarios in place ofd SUMPRODUCT,but I am unable to figure out how to do that.
Could somebody help me in writing the following formulae with SUMIFS.
SCENARIO-1:-
Tha following is the snbap shot of my data set.
I would like to use SUMIFS and get the desired result.I have used a formaula in cell F4 with SUMIFS but it is not yielding the correct result.

Excel Workbook
	A	B	C	D	E	F
1	Season	Location	Style	Category	Desired Result
2	AW08	Fresh	DBSU		8
3	AW08	Damage	DBSU	BELT	1
4	SS09	Fresh	BUPF		7	0
5	SS09	Damage	BUPF		0
6	AW09	Fresh	OPDB		4
7	AW09	Damage	OPDB		2
8	Total				22
9
10	Article Description	Qty	Location
11	AW09ETHACACBELT-OPDB-001FSS	2	Fresh
12	AW09ETHACACBELT-OPDB-001FSM	1	Fresh
13	AW09ETHACACBELT-OPDB-001FSL	1	Damage
14	AW09ETHACACBELT-OPDB-001FSXL	1	Fresh
15	AW09ETHACACBELT-OPDB-002FSS	1	Damage
16	Aw08ETHACACDENIM-DBSU-003FSM	5	Fresh
17	AW08ETHACACBELT-OPDB-002FSL	3	Fresh
18	Aw08ETHACACBELT-DBSU-003FSS	1	Damage
19	Aw08ETHACACDENIM-DBSU-003FSM	3	Fresh
20	AW09ETHACACBELT-DBSU-003FSL	5	Fresh
21	SS09ETHACACBELT-DTPF-004TSS	1	Fresh
22	AW09ETHACACBELT-DTPF-004TSM	3	Damage
23	SS09ETHACACBELT-DTPF-004TSL	2	Fresh
24	SS09ETHACACBELT-BUPF-001SSS	1	Fresh
25	SS09ETHACACBELT-BUPF-001SSM	6	Fresh
5

SCENARIO-2:-

I would like to write a formula in E8 using SUMIFS in place of SUMPRODUCT to derive the same result as shown in the data-set.
Is it possible?

Excel Workbook
	C	D	E	F	G	H	J
7		Period	Amount
8		0-15 Days	746220
9		16-20 Days	0
10		21-30 Days	0
11		>30 Days	0
12		Average Leadtime(In Working Days) For Bill Processing	17
13
14	Bill Date	Bill Receiving Date	Amount	Deductions	Approved Amount	Bill Submission Date	Payment Received On
15	2/28/2011	3/3/2011	687173	0	687173	3/3/2011	22-Mar
16	2/28/2011	3/3/2011	62788	0	62788	3/3/2011	22-Mar
17	2/28/2011	3/3/2011	29682	0	29682	3/3/2011	22-Mar
18	3/31/2011	4/4/2011	687173	0	687173	4/4/2011	25-Apr
19	3/31/2011	4/4/2011	80927	0	80927	4/4/2011	25-Apr
20	4/30/2011	5/9/2011	687174	0	687174	5/9/2011	6-Jun
21	4/30/2011	5/9/2011	76614	0	76614	5/9/2011	6-Jun
22	5/31/2011	6/2/2011	687174	0	687174	6/2/2011	28-Jun
23	5/31/2011	6/2/2011	42747	0	42747	6/2/2011	28-Jun
24	6/30/2011	7/1/2011	687173	7161	680012	7/1/2011	22-Jul
25	6/30/2011	7/1/2011	21817	0	21817	7/1/2011	22-Jul
26	7/30/2011	8/4/2011	687173	1838	685335	8/4/2011
27	7/31/2011	8/4/2011	60885	0	60885	8/4/2011
Total Bill

 

[Aladin Akyurek]

Scenario 1

=SUMPRODUCT(--(ISNUMBER(SEARCH(A2,A$11:A$25)+SEARCH(C2,A$11:A$25)+SEARCH(D2,$A$11:$A$25))),--(C$11:C$25=B2),B$11:B$25)

cannot be rewritten in terms of SUMIFS because of the OR search pattern involved.

Scenario 2

Enter the following in C8:C11...

<TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=69><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2446" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2824577 class=xl65 height=20 width=69 align=right>0</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 52pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=69 align=right>16</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 52pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=69 align=right>21</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 52pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=69 align=right>30</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 align=right>1.00E+308</TD></TR></TBODY></TABLE>

The latter is how Excel display 9.99999999999999E+307 which you need to enter in C11.

In K15 enter and copy down:

=TODAY()-H15

Now we can have in E8...

=SUMIFS($G$15:$G$27,$K$15:$K$27,">="&C8,$K$15:$K$27,"<"&C9,$J$15:$J$27,"=")

and copy this down to E10.

 

[xlmaniac]

Dear Sir,
Thank you once again for an absolutely amazing solution.
I have learnt a lot from your solutions and for that I am really indebted to you.
Pls accept my sincere gratitude for all your help.
Regards

 

[Aladin Akyurek]

Dear Sir,
Thank you once again for an absolutely amazing solution.
I have learnt a lot from your solutions and for that I am really indebted to you.
Pls accept my sincere gratitude for all your help.
Regards

You are welcome. Thanks for the kind feedback.