Formula to pull from a value from a category on a date that has 3 merged rows

**killerthun** · 01-17-2014, 10:20 AM

Hello,

I cant seem to get my formula to work properly, so I thought I would see if there were any other formulas i could use...
In the spreadsheet, there are 2 sheets. The first sheet had Dates vertically with data horozontally, the 2nd sheet is the opposite.
I need to pull the data of a category in sheet 1 from a date to the appropriate cell in sheet 2, the issue is that some of the data that I need to pull is 3 separate rows for one date.

I give an example of what I needed inside the file, if anyone could help out, it would be much appreciated.

Thanks in advance!

**daffodil11** · 01-17-2014, 10:29 AM

I did it with SUMPRODUCT.

C4:

=SUMPRODUCT((Sheet1!$E$1:$T$1=Sheet2!$A4)*(Sheet1!$A$2:$A$16=Sheet2!C$1)*(Sheet1!$E$2:$T$16))

And copied around

C8 is a little different. Are you just interested in the indirect of regular, as your example shows, or should it include all indirect?

**killerthun** · 01-17-2014, 10:53 AM

Just regular

**killerthun** · 01-17-2014, 10:56 AM

C4:

=SUMPRODUCT((Sheet1!$E$1:$T$1=Sheet2!$A4)*(Sheet1!$A$2:$A$16=Sheet2!C$1)*(Sheet1!$E$2:$T$16))

Tried this, and it returned the cell with a 0. It should be 20.

**daffodil11** · 01-17-2014, 11:21 AM

Ahh sorry, I forgot that I unmerged the cells and filled in the blanks with values from above.

After unmerging, the shortcut is to select the range (in this case A2:A16), hit F5, choose blanks, hit okay, immediately hit = and click on the first cell above the topmost blank cell (A2) and then hit Ctrl+Enter.

This fills in every row with the value above it if it is blank.

C4:

=SUMPRODUCT((Sheet1!$E$1:$T$1=Sheet2!$A4)*(Sheet1!$A$2:$A$16=Sheet2!C$1)*(Sheet1!$E$2:$T$16))

and copied to G7

C8:

=SUMPRODUCT((Sheet1!$A$2:$A$16=Sheet2!C$1)*(Sheet1!$D$2:$D$16=Sheet2!$A8)*(Sheet1!$E$2:$E$16))

and copied to G9

Please see the attached.
Copy of Test(1).xlsx

**killerthun** · 01-17-2014, 12:39 PM

Thanks. It works!

Formula to pull from a value from a category on a date that has 3 merged rows

LinkBack

Thread Tools

Rate This Thread

Display

Formula to pull from a value from a category on a date that has 3 merged rows

Re: Formula to pull from a value from a category on a date that has 3 merged rows

Re: Formula to pull from a value from a category on a date that has 3 merged rows

Re: Formula to pull from a value from a category on a date that has 3 merged rows

Re: Formula to pull from a value from a category on a date that has 3 merged rows

Re: Formula to pull from a value from a category on a date that has 3 merged rows

Thread Information

Users Browsing this Thread

Similar Threads

How can you look up how much each category has made into the category column? Formula?

[SOLVED] Formula or sorting method to pull apart merged data

array formula to return sum of values based on date and category code

Fill category field if date macro/formula

How do I pull a merged cell from another workbook?

Bookmarks

Bookmarks

Posting Permissions