Summary Report

[abhaydharma]

Hello Folks

Need some urgent help for one of the reports i am developing. It basically looking up date from row in to column and plant looking from column to column and summing up qty column.


INPUT DATA IS

[pre]

Pl 	DATE	       QTY
A	01.05.2013	3
A	01.05.2013	4
C	02.05.2013	5
E	03.05.2013	3
F	03.05.2013	8
A	01.05.2013	9
H	05.05.2013	1
B	02.05.2013	5
H	04.05.2013	7
C	02.05.2013	8
A	05.05.2013	10

Output required is

01.05.2012	02.05.2012	03.05.2012
A	7
B		           5
C		           13
D
E
F

[/pre]
Your help is appreciated..

 

[NARAYANK991]

Hi Abhay ,


Can you check the file here ?


https://www.dropbox.com/s/kvni5erhvnu1wsh/Dharma_Example.xlsx


Please note that I have used the SUMIFS function , which is available only in Excel 2007 and later.


Narayan

 

[abhaydharma]

Thanks for the help so fast. I appreciate it. But somehow i am not getting the result.

Also Is there any way we can use sumproduct here?

 

[NARAYANK991]

Hi Abhay ,


I have uploaded a workbook ; can you clearly post what you see on your computer when you open the workbook ?


A sentence such as :


But somehow i am not getting the result. is not of much use in trying to understand what the problem is.


Narayan

 

[Hui]

Abhaydharma


Firstly, Welcome to the Chandoo.org Forums


Can you please take a few minutes to read the rules of this forum at: http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting


Narayan's formula works fine

Did you make it larger for your data range which I suspects goes beyond Row 13?

The range lengths all need to be the same length eg: A3:A13, B3:B13, C3:C13


Narayan's formula also uses the Sumifs function, which wasn't available before Excel 2007 and could explain why it doesn't work for you if you have an older version.


If you want to use Sumproduct

in Sheet2!B2: =SUMPRODUCT((Sheet1!$A$3:$A$13=$A2)*(Sheet1!$B$3:$B$13=B$1)*Sheet1!$C$3:$C$13)


Copy down / across


Narayan's formula gave 0'ss where there was no entry, your sample post shows blanks

You can either use a Custom Number format of #.0;-#.0;;

or

a Custom Format to make the cell have the same font color as the background when the value is 0 to hide the 0's


Finally you will see that I have renamed your post

Although your problem may be Urgent for you, it certainly isn't for the rest of us.

A descriptive Title will attract people with the right skills to help you, quicker.

It also makes searching for a problem easier later.

 

[abhaydharma]

Hui


Thanks for the info. The solutions really helped.