Glenn_Turner
New Member
- Joined
- Dec 1, 2016
- Messages
- 3
Hi,
I have been trying to sumproduct cells with one row and two column search criteria. C3:K29 is a pivot table from a networked cube data source. It shows date data in A:C and location parameters in rows 7:10. This is a very small section of my original data set, which has several combinations of the column headers you see below plus others that i have excluded from this sample.
Column A is a date i created as the date in the pivot table is not recognised as a date by excel, even with formatting. I want this date to start with the start of the current month (=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1))
Column B combines the date from column A and adds it to the shift identifier in column C as i need to break information down to day and night shift (=A12&C12)
My first sum product works as I simplified one level of search parameters by combining SH01 & Crusher in row 1 of the sheet, but i would still like to also search for ROM and ROM-PR.
I want to search for a date in Column A, I then want to search the column headers to find othes, in this case SH01& stockpile & ROM & ROM-PR, then sum the values for those which are true
I can get a sum product formula to work if i only want to search the date and one of the identifiers for the top row, but when i add another for looking for both ROM and ROM-PR it gives me a #N/A value
My formulas area at the bottom, im sure it is something simple to do with trying to add additional search parameters. Thanks in advance.
Sheet2
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:110px;"><col style="width:161px;"><col style="width:117px;"><col style="width:139px;"><col style="width:117px;"><col style="width:139px;"><col style="width:110px;"><col style="width:110px;"><col style="width:118px;"><col style="width:131px;"></colgroup><tbody>
</tbody>
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
I have been trying to sumproduct cells with one row and two column search criteria. C3:K29 is a pivot table from a networked cube data source. It shows date data in A:C and location parameters in rows 7:10. This is a very small section of my original data set, which has several combinations of the column headers you see below plus others that i have excluded from this sample.
Column A is a date i created as the date in the pivot table is not recognised as a date by excel, even with formatting. I want this date to start with the start of the current month (=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1))
Column B combines the date from column A and adds it to the shift identifier in column C as i need to break information down to day and night shift (=A12&C12)
My first sum product works as I simplified one level of search parameters by combining SH01 & Crusher in row 1 of the sheet, but i would still like to also search for ROM and ROM-PR.
I want to search for a date in Column A, I then want to search the column headers to find othes, in this case SH01& stockpile & ROM & ROM-PR, then sum the values for those which are true
I can get a sum product formula to work if i only want to search the date and one of the identifiers for the top row, but when i add another for looking for both ROM and ROM-PR it gives me a #N/A value
My formulas area at the bottom, im sure it is something simple to do with trying to add additional search parameters. Thanks in advance.
Sheet2
* | A | B | C | D | E | F | G | H | I | J | K |
1 | * | * | * | SH01Crusher | SH01Crusher | SH01Crusher | SH01Crusher | SH01Dump | SH01Dump | SH01Stockpile | SH01Stockpile |
2 | * | * | * | SH01 | SH01 | SH01 | SH01 | SH01 | SH01 | SH01 | SH01 |
3 | * | * | * | Crusher | Crusher | Crusher | Crusher | Dump | Dump | Stockpile | Stockpile |
4 | * | * | * | CR01 NTH | CR01 NTH | CR01 STH | CR01 STH | 7WD340_NN | 7WD340_PAF | 7WD340_PAF | GSS CRUSHER PAD |
5 | * | Row Labels | Row Labels | ROM | ROM-PR | ROM | ROM-PR | NDOL | NDOL | ROM-PR | ROM |
6 | 42675 | 4267501-NOV-16 | 01-NOV-16 | * | * | * | * | * | * | * | * |
7 | 42675 | 42675D | D | 0 | 2086 | 0 | 27740 | 0 | 0 | 0 | 0 |
8 | 42675 | 42675N | N | 0 | 1043 | 0 | 25863 | 0 | 0 | 0 | 0 |
9 | 42676 | 4267602-NOV-16 | 02-NOV-16 | * | * | * | * | * | * | * | * |
10 | 42676 | 42676D | D | 0 | 9386 | 0 | 6049 | 0 | 0 | 0 | 0 |
11 | 42676 | 42676N | N | 0 | 0 | 0 | 18771 | 0 | 0 | 0 | 0 |
12 | 42677 | 4267703-NOV-16 | 03-NOV-16 | * | * | * | * | * | * | * | * |
13 | 42677 | 42677D | D | 0 | 4589 | 0 | 13766 | 0 | 0 | 0 | 0 |
14 | 42677 | 42677N | N | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
15 | 42678 | 4267804-NOV-16 | 04-NOV-16 | * | * | * | * | * | * | * | * |
16 | 42678 | 42678D | D | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
17 | 42678 | 42678N | N | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
18 | 42679 | 4267905-NOV-16 | 05-NOV-16 | * | * | * | * | * | * | * | * |
19 | 42679 | 42679D | D | 0 | 1563 | 1877 | 0 | 0 | 0 | 1000 | 1640 |
20 | 42679 | 42679N | N | 209 | 209 | 9386 | 6049 | 0 | 0 | 0 | 0 |
21 | 42680 | 4268006-NOV-16 | 06-NOV-16 | * | * | * | * | * | * | * | * |
22 | 42680 | 42680D | D | 0 | 1043 | 0 | 12097 | 0 | 0 | 0 | 0 |
23 | 42680 | 42680N | N | 0 | 0 | 19606 | 13974 | 0 | 0 | 0 | 0 |
24 | 42681 | 4268107-NOV-16 | 07-NOV-16 | * | * | * | * | * | * | * | * |
25 | * | * | * | * | * | * | * | * | * | * | * |
26 | * | * | * | * | * | * | * | * | * | * | * |
27 | * | * | 05-Nov-16 | D | 42679D | * | * | * | * | * | * |
28 | * | * | * | * | * | * | * | * | * | * | * |
29 | * | SH01 | Crusher | SH01Crusher | 3440.13 | * | * | * | * | * | * |
30 | * | SH01 | Stockpile | SH01Stockpile | #N/A | * | * | * | * | * | * |
31 | * | ROM | * | * | * | * | * | * | * | * | * |
32 | * | ROM-PR | * | * | * | * | * | * | * | * | * |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:51px;"><col style="width:110px;"><col style="width:161px;"><col style="width:117px;"><col style="width:139px;"><col style="width:117px;"><col style="width:139px;"><col style="width:110px;"><col style="width:110px;"><col style="width:118px;"><col style="width:131px;"></colgroup><tbody>
</tbody>
Spreadsheet Formulas | ||||||||||
<tbody> </tbody> |
<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4