Sumif formula

[hrayani]

Hi Everyone

I need some formula for the below mention Data

Sheet1 Name = DATA
Sheet2 Name = REPORTS

DATA

	A	B	C	D	E	F	G	H	I	J	K
5	CLIENT	Page #	File #	DATE	Unit	P/Unit	Stock	STOCK IN	STOCK OUT	CLOSING Stock	Rent
6	Star Plastic	1	0	15-Sep-10	Bags	2.00	-	664	-	664	1,328
7	Star Plastic	1	0	15-Sep-10	Bags	2.00	664	-	664	-
8
9	Adnan Data	2	0	15-Sep-10	Bags	2.00	-	7,920	-	7,920	15,840
10	Adnan Data	2	0	15-Sep-10	Bags	2.00	7,920	-	200	7,720
11	Adnan Data	2	0	21-Sep-10	Bags	2.00	7,720	-	100	7,620
12	Adnan Data	2	0	24-Sep-10	Bags	2.00	7,620	-	250	7,370
13	Adnan Data	2	0	25-Sep-10	Bags	2.00	7,370	-	250	7,120
14	Adnan Data	2	0	28-Sep-10	Bags	2.00	7,120	-	50	7,070
15	Adnan Data	2	0	15-Oct-10	Bags	2.00	7,070	-	-	7,070	14,140
16	Adnan Data	2	0	15-Oct-10	Bags	2.00	7,070	-	55	7,015
17	Adnan Data	2	0	23-Oct-10	Bags	2.00	7,015	-	50	6,965
18	Adnan Data	2	0	28-Oct-10	Bags	2.00	6,965	-	50	6,915
19	Adnan Data	2	0	30-Oct-10	Bags	2.00	6,915	-	100	6,815
20	Adnan Data	2	0	04-Nov-10	Bags	2.00	6,815	-	195	6,620

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 87px"><COL style="WIDTH: 44px"><COL style="WIDTH: 37px"><COL style="WIDTH: 70px"><COL style="WIDTH: 45px"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"><COL style="WIDTH: 68px"><COL style="WIDTH: 80px"><COL style="WIDTH: 101px"><COL style="WIDTH: 75px"> </COLGROUP><TBODY>
</TBODY>

REPORTS

	B	C
3	ACCOUNT STATEMENT
4
5	CLIENT	Faheem Traders
6
7
8	STOCKS	DATES
9	Start	18-Nov-11
10	End	29-May-13
11
12
13
14	STOCKS	QUANTITY
15	In	9,067
16	Out	8,794
17	Balance	273
18
19
20
21	RENT	AMOUNT
22	Charged	185,220
23	Received	-
24	Balance	185,220

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 103px"><COL style="WIDTH: 201px"> </COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula C9 {=MIN(IF((DATA!$A$6:$A$10000=$C$5),IF(ISNUMBER(DATA!$D$6:$D$10000),DATA!$D$6:$D$10000)))} C10 {=MAX(IF((DATA!$A$6:$A$10000=$C$5),IF(ISNUMBER(DATA!$D$6:$D$10000),DATA!$D$6:$D$10000)))} C15 {=SUM(IF(DATA!$A$6:$A$10000=$C$5,DATA!$H$6:$H$10000,0))} C16 {=SUM(IF(DATA!$A$6:$A$10000=$C$5,DATA!$I$6:$I$10000,0))} C17 =+C15-C16 C22 {=SUM(IF(DATA!$A$6:$A$10000=$C$5,DATA!$K$6:$K$10000,0))} C24 =+C22-C23 <TBODY> </TBODY>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

When i enter a client in cell C5 then the dates, Stock in out and rent cells are showing perfect results.. What i need is when i enter "ALL" in cell C5 then it should show the total data present in the DATA sheet

Hope anyone can help

Regards,

Humayun

 

[LxQ]

I tried it with adding an OR($C$5="All",

for example, C16:

=SUM(IF(OR($C$5="All",DATA!$A$6:$A$10000=$C$5),DATA!$I$6:$I$10000,0))

 

[hrayani]

Thanks Lxq for your reply

i found one myself which is a bit lengthy

=SUM(IF(DATA!$B$6:$B$10000=$C$5,IF(DATA!$F$6:$F$10000=D$9,IF(DATA!$E$6:$E$10000>=$K$5,IF(DATA!$E$6:$E$10000<=$K$6,DATA!$I$6:$I$10000)))),SUM(IF(DATA!$A$6:$A$10000=$C$5,IF(DATA!$F$6:$F$10000=D$9,IF(DATA!$E$6:$E$10000>=$K$5,IF(DATA!$E$6:$E$10000<=$K$6,DATA!$I$6:$I$10000))))))

679,020

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[Aladin Akyurek]

Thanks Lxq for your reply

i found one myself which is a bit lengthy

=SUM(IF(DATA!$B$6:$B$10000=$C$5,IF(DATA!$F$6:$F$10000=D$9,IF(DATA!$E$6:$E$10000>=$K$5,IF(DATA!$E$6:$E$10000<=$K$6,DATA!$I$6:$I$10000)))),SUM(IF(DATA!$A$6:$A$10000=$C$5,IF(DATA!$F$6:$F$10000=D$9,IF(DATA!$E$6:$E$10000>=$K$5,IF(DATA!$E$6:$E$10000<=$K$6,DATA!$I$6:$I$10000))))))

679,020

<tbody>
</tbody>

Shouldn't that be just...

=SUM(IF(DATA!$B$6:$B$10000=$C$5,IF(DATA!$F$6:$F$10000=D$9,IF(DATA!$E$6:$E$10000>=$K$5,IF(DATA!$E$6:$E$10000<=$K$6,DATA!$I$6:$I$10000))))

appropriate or not for the task at hand?