Cell returning sum of multiple values in MULTIPLE COLUMNS

jjhorst

New Member
Joined
Oct 15, 2018
Messages
2
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.


Product
Date 1
Production 1
Date 2
Production 2
Date 3
Prod 3
etc
Cabbage
11/30/2018
5
0
0
01/31/2019
7
Turnip
0
0
12/31/2018
6
01/31/2019
9
Onion
12/31/2018
9
01/31/2019
6
01/31/2019
8
Cabbage
0
0
12/31/2018
4
01/31/2019
6
Onion
0
0
11/30/2018
812/31/20196.5

<tbody>
</tbody>

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

Date
Onion
Turnip
Cabbage
11/30/2018
8
0
5
12/31/2018
15.5
6
4
01/31/2019
14
9
13
02/28/2019
...

<tbody>
</tbody>

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

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,724
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)
 
Last edited:

jjhorst

New Member
Joined
Oct 15, 2018
Messages
2
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!

(y) Cheers m8 ! Owe you a beer!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,092
Messages
5,628,620
Members
416,329
Latest member
phxdan79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top