Cell returning sum of multiple values in MULTIPLE COLUMNS

[jjhorst]

Hi

I'm breaking my head on an issue regarding multiple columns returning values according to date - but each column has a different 'Date' sort of speaking.


[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]Product
[/TD]
[TD]Date 1
[/TD]
[TD]Production 1
[/TD]
[TD]Date 2
[/TD]
[TD]Production 2
[/TD]
[TD]Date 3
[/TD]
[TD]Prod 3
[/TD]
[TD]etc
[/TD]
[/TR]
[TR]
[TD]Cabbage
[/TD]
[TD]11/30/2018
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]01/31/2019
[/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Turnip
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]12/31/2018
[/TD]
[TD]6
[/TD]
[TD]01/31/2019
[/TD]
[TD]9
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Onion
[/TD]
[TD]12/31/2018
[/TD]
[TD]9
[/TD]
[TD]01/31/2019
[/TD]
[TD]6
[/TD]
[TD]01/31/2019
[/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cabbage
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]12/31/2018
[/TD]
[TD]4
[/TD]
[TD]01/31/2019
[/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Onion
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]11/30/2018
[/TD]
[TD]8[/TD]
[TD]12/31/2019[/TD]
[TD]6.5
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

And the table I'm trying to calculate would be something like this :

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Onion
[/TD]
[TD]Turnip
[/TD]
[TD]Cabbage
[/TD]
[/TR]
[TR]
[TD]11/30/2018
[/TD]
[TD]8
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]12/31/2018
[/TD]
[TD]15.5
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]01/31/2019
[/TD]
[TD]14
[/TD]
[TD]9
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]02/28/2019
[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So the nightmare is:

- Dates are consistent but not continuous;
- Production types are derived from previous calculations and can't be hardcoded/changed;
- I actually have seven combos date x production;

I need a table with the conditional sum of products, sometimes multiple lines and rows will provide, sometimes only one value, and so on...

I've tried some combinations of VLOOKUP and SUMIF (sometimes SUMIFS) but I always reach a wall regarding the different columns...

Well, I hope there's some light on the end of the tunnel ...

 

[mrshl9898]

SUMIFS should do it, but you'd need one for each column with a number.


I put the table on a sheet called "Input" with "Product" in A1

In another sheet the table with "Date" in A1

In B2, then copied to other cells:

=SUMIFS(Input!$C:$C,Input!$B:$B,$A2,Input!$A:$A,B$1)+SUMIFS(Input!$E:$E,Input!$D:$D,$A2,Input!$A:$A,B$1)+SUMIFS(Input!$G:$G,Input!$F:$F,$A2,Input!$A:$A,B$1)

 

[jjhorst]

Thank you very much, it does seemed to work!

The actual input was already the max I could 'rearrange' the actual original input! But from this step forwards, pretty much everything will work much better!

Cheers m8 ! Owe you a beer!