• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to design the data?

G

GN0001

Member
Hello team,
I have put a sumproduct function to summarize the data.
How can I put the data/ ranges in the spread sheet to bring every thing.

This is my range:
Name Project Name Date Value SumProducts returns
George A First Week 1 1
Rita B Second Week 2 0
shawn C Third Week 3 0
kami A First Week 4 4
India B Second Week 5 0
Sam C Third Week 6 0
Sita A First Week 7 7

Why do I get 0 for second week for project Name B for Rita?
 

Attachments

  • SumProduct.xlsx
    9.3 KB · Views: 11
Hi ,

It all depends on what you wish to do. Without knowing that , your formula can be changed to return any value of your choice.

For example , change it to :

=SUMPRODUCT(($B$6:$B$12=$B6)*($C$6:$C$12=$C6)*($D$6:$D$12=$D6)*$E$6:$E$12)

Narayan
 
Hi Gn0001!

As per my guessing power..

=SUMPRODUCT(($C$6:$C$12=$C12)*($D$6:$D$6=$D12)*($B$6:$B$12=$B12)*($D$6:$D$12=$D12)*$E$6:$E$12)

You have to re-check highlighted red area..
 
Debraj said:
Hi Gn0001!

As per my guessing power..

=SUMPRODUCT(($C$6:$C$12=$C12)*($D$6:$D$6=$D12)*($B$6:$B$12=$B12)*($D$6:$D$12=$D12)*$E$6:$E$12)

You have to re-check highlighted red area..
Click to expand...
NARAYANK991 said:
Hi ,

It all depends on what you wish to do. Without knowing that , your formula can be changed to return any value of your choice.

For example , change it to :

=SUMPRODUCT(($B$6:$B$12=$B6)*($C$6:$C$12=$C6)*($D$6:$D$12=$D6)*$E$6:$E$12)

Narayan
Click to expand...
Hello Narayan and DEB,
I have done exactly what you have done, but it doesn't bring back the result, I can't tell you how much time I have spent on it, the whole afternoon today, right now it is 12:18 a.m. and all Friday afternoon.
Eventually I took it to Access to group and summarize it.

I did SumIfs and that didn't work either.

This is my formula:
=SUMIFS('BB (2)'!$G$3:$G$6570,'BB (2)'!$C$3:$C$6570,$C3,'BB (2)'!$A$3:$A$6570,$A3,'BB (2)'!$E$3:$E$6570,$F$2)

Just it doesn't work, I have a column as Apr-01-2014, should I use a function to get the value of the date column or it is not needed? Even I didn't copy and paste the values from one sheet to another sheet.
I created a copy of the sheet and removed the duplicate values and then my range was in sheet 1 and criteria was on sheet 2.

I doesn't bring the correct value. What the problem is?
Thank you for the help.
GN0001
 
Hi ,

As I have already posted , unless you tell us what you want to do , how can we correct the formula ?

For example , the formula you have just now posted is :

=SUMIFS('BB (2)'!$G$3:$G$6570,'BB (2)'!$C$3:$C$6570,$C3,'BB (2)'!$A$3:$A$6570,$A3,'BB (2)'!$E$3:$E$6570,$F$2)

Should that be E or F ? I do not know.

The above formula is summing those values in column G , where the data in column C matches the data in cell C3 , the data in column A matches that in cell A3 , and the data in column E matches that in cell E3 ( F3 ). Of course C3 , A3 and E3 ( F3 ) are in one sheet , while the data is in the tab named BB (2).

Only you can say whether this is the correct formula to do whatever you wish to do.

Narayan
 
NARAYANK991 said:
Hi ,

As I have already posted , unless you tell us what you want to do , how can we correct the formula ?

For example , the formula you have just now posted is :

=SUMIFS('BB (2)'!$G$3:$G$6570,'BB (2)'!$C$3:$C$6570,$C3,'BB (2)'!$A$3:$A$6570,$A3,'BB (2)'!$E$3:$E$6570,$F$2)

Should that be E or F ? I do not know.

The above formula is summing those values in column G , where the data in column C matches the data in cell C3 , the data in column A matches that in cell A3 , and the data in column E matches that in cell E3 ( F3 ). Of course C3 , A3 and E3 ( F3 ) are in one sheet , while the data is in the tab named BB (2).

Only you can say whether this is the correct formula to do whatever you wish to do.

Narayan
Click to expand...

I have a column that is contains project name 6569 rows, but only 180 of them is unique and I have employees that has 6569 rows, but only 280 of them is unique and I have a column date that has 6569 rows and contains values as Apr-01-2014, Apr-07-2014, Apr-014-2014 till the end of May.

I have hour column that has values/ numbers has 6569 rows.
and I copy and paste the date column across a row, and I did a sumproduct, exactly what I did in the uploaded files, it just doesn't bring the values and i can't understand why, I tried not to involve column date, but it still desn't return the correct sum. The criteria and data sources (ranges) are in two different sheets.

I have to find out why it doesn't work.

My column containing values as: Apr-01-2014 on the sheet that has criteria runs across the row and it is on F2. Hope this is clear.
Regards,
GN0001
 
Hi ,

Your uploaded file was not a sample of your real-life working file ; if you can remove all of the data leaving around 50 rows of data , and upload that file , it will make it easy.

Narayan
 
The data belongs to a company. Let me see what I can do,
Anyway, before I go to bed, I did the function one more time, and it didn't work, this is the function:
=SUMPRODUCT((A!$A$2:$A$6569=B!$A2)*(A!$C$2:$C$6569=B!$C2)*(A!$E$2:$E$6569=B!$F$1)*A!$G$2:$G$6569)
Try to change the data and sent it across.
Regards,
GN0001
 
Hello Narayan and DEB,
Ok, I realized why the formula is not working, The SumProduct and SumIfs formula that I entered were correct.
I didn't map the correct cells to each other.

These are my columns:

EmployeeName ProjectName Date Amount

George ProjectA Apr-12-2012 3
Jack ProjectB Apr-16-2012 5
Arta ProjectA Apr-19-2012 3
George ProjectC Apr-12-2012


I have 6000 rows and they are duplicates, when I removed the duplicates out of these columns to use them as criteria, the values are not mapped correctly.

If I remove duplicates from EmployeeName I would have only one George and then the combination of George and Projectc will be lost.

So I need to know how to display the criteria.

If this is not clear, I will post a file very soon.

Thanks,
GN0001
 
Hello Debraj,

I have 6000 rows, I have 350 unique Project names and I have unique 120 Project Managers, and I have 16 unique dates. These 6000 rows contains different combinations of project managers and project names and dates. How can I design the data that I can capture all the various cases?

What I did in the past, I copy and pasted the column Project names, project managers in two adjacent column and dates run across one row. Then SumProduct was picking up two cells that were not related to each other at all and as a result formula was returning zero.

When I copy and pasted the unique values, I made the Project Manager A to be mapped to Project name c. while in the original data set, the project Manger A was mapping to Project Name b.

Let me know if this is not clear.

GN0001
 
You must log in or register to reply here.
Back
Top