• 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.

Quite advanced excel question

V

Viktoriya

New Member
Each week our company receives standard report which can't be modified (i cant add formulas of modify report itself, only can add sheets).

Description 01/11 02/11 03/11 04/11

"All-in-one C340" 10 1 20 4

"All-in-one C345" 10 20

The only thing I need is to sum values in rows if dates in headers belong to the particular week, but there can be several columns which meet the criteria.

There are dates in headers, but in odd format. So I formatted them to find out the week number. This mapping is done on the separate spreadsheet:

=WEEKNUM(MID($Z3,4,2)&"/"&LEFT($Z3,2)&"/"&RIGHT($Z3,4),2)

Data WeekNum

01/11/2014 44

02/11/2014 44

03/11/2014 45

So, I can't make up my mind which formula to use to calculate:

  • I need to have "Control cell" (on another sheet) where i can choose the weeknumber i need (from 1 to 52), and the formula will automatically match the weeknumber with dates in columns, choose ALL needed columns to sum results in rows for particular description, for example for `"All-in-one C340".

  • one more peculiarity of the report is that number of rows and column can change every week. so I need formula without specified range (I mean not A2:A20, but A:B).
I tried formula {=SUM(IF(Description="All-in-one C340"),A:Z))}, and it works fine, but calculates all values in area for this specific description.

How can this formula be modified to search for particular columns?

If this is wrong formula to use, please help me with another.
 

Attachments

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