I am having a bit of a struggle with excel. I have a workbook with 20 sheets, that all have the same format and identical structure. I have a cell with a date and a table with stocks and their total. I need to SUM all the totals if the date of the cell in the sheet is today(or whatever date i would like). I have already tried with =SUMIF(Sheet1:Sheet!C2, D1(the date i set), Sheet1:Sheet3!B5) and couple of variatons of SUMIF but it keeps returning me #VALUE.
Here is a data example:
Sheet1:
|--|-----------|-----------|-----------|
|--| A | B | C |
|1 | Product | Stock | |
|--|-----------|-----------|-----------|
|2 | Shoes | 34 |31-01-2018 |
|--|-----------|-----------|-----------|
|3 | Hats | 23 | |
|--|-----------|-----------|-----------|
|4 | Jackets | 12 | |
|--|-----------|-----------|-----------|
|5 | Total | 69 | |
|--|-----------|-----------|-----------|
Sheet2:
|--|-----------|-----------|-----------|
|--| A | B | C |
|1 | Product | Stock | |
|--|-----------|-----------|-----------|
|2 | Shoes | 38 |30-01-2018 |
|--|-----------|-----------|-----------|
|3 | Hats | 25 | |
|--|-----------|-----------|-----------|
|4 | Jackets | 16 | |
|--|-----------|-----------|-----------|
|5 | Total | 79 | |
|--|-----------|-----------|-----------|
Sheet3:
|--|-----------|-----------|-----------|
|--| A | B | C |
|1 | Product | Stock | |
|--|-----------|-----------|-----------|
|2 | Shoes | 30 |31-01-2018 |
|--|-----------|-----------|-----------|
|3 | Hats | 20 | |
|--|-----------|-----------|-----------|
|4 | Jackets | 10 | |
|--|-----------|-----------|-----------|
|5 | Total | 60 | |
|--|-----------|-----------|-----------|
May be I should mention that the C2s are date formatted. How can I SUM the totals of Sheet1 and Sheet3 depending on the date 31-02-2018? I already searched a lot but no success. I am not sure if I am not using SUMIF properly or it's just not the right choice of formula. I would be grateful for any help. Thank you in advance!
=Sumif(Sheet1!C2:C100, D1, Sheet1!B2:B100) + Sumif(Sheet2!C2:C100, D1, Sheet2!B2:B100) + ...
Or have a sumif on each page that uses the one date then sum those up in your final tab. That, or you will have to write sum VBA, but.. that would get uglier than a good set of formulas to do this. – JNevill