Sumif formula

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,516
Office Version
  1. 2016
Platform
  1. Windows
Hi Everyone

I need some formula for the below mention Data

Sheet1 Name = DATA
Sheet2 Name = REPORTS

DATA

ABCDEFGHIJK
CLIENTPage #File #Unit

<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>
[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: center"]DATE[/TD]

[TD="align: center"]P/Unit[/TD]
[TD="align: right"]Stock [/TD]
[TD="align: right"]STOCK IN [/TD]
[TD="align: right"]STOCK OUT [/TD]
[TD="align: right"]CLOSING Stock [/TD]
[TD="align: right"]Rent [/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #c0c0c0, align: left"]Star Plastic[/TD]
[TD="bgcolor: #c0c0c0, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]15-Sep-10[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Bags[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2.00[/TD]
[TD="bgcolor: #c0c0c0, align: right"] - [/TD]
[TD="bgcolor: #c0c0c0, align: right"] 664 [/TD]
[TD="bgcolor: #c0c0c0, align: right"] - [/TD]
[TD="bgcolor: #c0c0c0, align: right"] 664 [/TD]
[TD="bgcolor: #c0c0c0, align: right"] 1,328 [/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]Star Plastic[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15-Sep-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 664 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 664 [/TD]
[TD="align: right"] - [/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="bgcolor: #c0c0c0, align: left"]Adnan Data[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2[/TD]
[TD="bgcolor: #c0c0c0, align: center"]0[/TD]
[TD="bgcolor: #c0c0c0, align: center"]15-Sep-10[/TD]
[TD="bgcolor: #c0c0c0, align: center"]Bags[/TD]
[TD="bgcolor: #c0c0c0, align: center"]2.00[/TD]
[TD="bgcolor: #c0c0c0, align: right"] - [/TD]
[TD="bgcolor: #c0c0c0, align: right"] 7,920 [/TD]
[TD="bgcolor: #c0c0c0, align: right"] - [/TD]
[TD="bgcolor: #c0c0c0, align: right"] 7,920 [/TD]
[TD="bgcolor: #c0c0c0, align: right"] 15,840 [/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15-Sep-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,920 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 200 [/TD]
[TD="align: right"] 7,720 [/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]21-Sep-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,720 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 100 [/TD]
[TD="align: right"] 7,620 [/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]24-Sep-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,620 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 250 [/TD]
[TD="align: right"] 7,370 [/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]25-Sep-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,370 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 250 [/TD]
[TD="align: right"] 7,120 [/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]28-Sep-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,120 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 50 [/TD]
[TD="align: right"] 7,070 [/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15-Oct-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,070 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 7,070 [/TD]
[TD="align: right"] 14,140 [/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]15-Oct-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,070 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 55 [/TD]
[TD="align: right"] 7,015 [/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]23-Oct-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 7,015 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 50 [/TD]
[TD="align: right"] 6,965 [/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]28-Oct-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 6,965 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 50 [/TD]
[TD="align: right"] 6,915 [/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]30-Oct-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 6,915 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 100 [/TD]
[TD="align: right"] 6,815 [/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: left"]Adnan Data[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]04-Nov-10[/TD]
[TD="align: center"]Bags[/TD]
[TD="align: center"]2.00[/TD]
[TD="align: right"] 6,815 [/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 195 [/TD]
[TD="align: right"] 6,620 [/TD]

</TBODY>




REPORTS

BC
ACCOUNT STATEMENT
Start
End
In
Out
Balance
RENT
Charged
Received
Balance

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 103px"><COL style="WIDTH: 201px"> </COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]CLIENT[/TD]
[TD="align: left"]Faheem Traders[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]STOCKS[/TD]
[TD="align: right"]DATES[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]18-Nov-11[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]29-May-13[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]STOCKS[/TD]
[TD="align: right"]QUANTITY[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"] 9,067 [/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"] 8,794 [/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="align: right"] 273 [/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="align: right"]AMOUNT[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="align: right"] 185,220 [/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="align: right"] - [/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="align: right"] 185,220 [/TD]

</TBODY>

Spreadsheet Formulas
CellFormula
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
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))
 
Upvote 0
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))))))
[TABLE="width: 112"]
<TBODY>[TR]
[TD] 679,020[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
 
Upvote 0
hrayani said:
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))))))
[TABLE="width: 112"]
<tbody>[TR]
[TD] 679,020
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
INDEX and MATCH formula for rainfall cessation
Replies
6
Views
280
Eric W
Eric W
M
  • Question Question
Index Match function List based on date
Replies
7
Views
243
Fluff
Fluff
F
  • Solved
Formula - Sumif(s)? Sumproduct? etc.
2
Replies
16
Views
247
Fluff
Fluff
M
  • Question Question
Customer Ageing Different Formulas
Replies
3
Views
227
Alex Blakenburg
A
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
848
Cubist
Cubist

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top