1

Suppose I have the following table in sheet month:

Table example

And the following table in sheet __data:

Table2 example

In sheet __data I'd like to sum all the values from column P,R,T,V and X, for the given category name.

For a single row, I know I can use the following formula: (cat6 for example)

=IF(
    SUMIF(month!$O$5:$O$34;__data!$A7;month!$P$5:$P$34)<>0;
    SUMIF(month!$O$5:$O$34;__data!$A7;month!$P$5:$P$34);
    "")

Now, I believe this should be possible with SUMPRODUCT. But I can't wrap my head around how this should be coded.

I have this SUMPRODUCT formula, but this results in an #VALUE! error;

=SUMPRODUCT(
    --(month!$O$5:month!$O$34=__data!$A7);
    (
        month!$P$5:month!$P$34 +
        month!$R$5:month!$R$34 + 
        month!$T$5:month!$T$34 + 
        month!$V$5:month!$V$34 + 
        month!$X$5:month!$X$34)
)

I believe that this has something to do with the way how the values in P,R,T,V and X are populated. This is done with the following formula;

=IF(SUMIF($B$5:$B$123;O8;$D$5:$D$123)<>0;SUMIF($B$5:$B$123;O8;$D$5:$D$123);"")
2
  • I think the problem is related the data source in Sheet month, did the column O contain any Spaces?? Try to remove them.
    – Lee
    Commented Oct 16, 2019 at 9:24
  • That column is populated with this formula =IF(ISTEXT(__gegevens!B18);__gegevens!B18;""). So you're saying that that's the problem?
    – Mathlight
    Commented Oct 16, 2019 at 9:50

2 Answers 2

1

You can easily do this with one formula. But you need to have a method to return discontinuous columns.

  • I named the table of data catTbl to make it easier to refer to.
  • Find the row using the MATCH function:
    • =MATCH(A2,INDEX(catTbl,0,1),0)
      • The category is in A2, and the INDEX function returns all rows in the first column.
    • The construct N(IF(1,{2,4,6,8,10})) will return the relevant columns to theINDEX` function.
    • Then just SUM the array

So the entire formula:

=SUM(INDEX(catTbl,MATCH(A2,INDEX(catTbl,0,1),0),N(IF(1,{2,4,6,8,10}))))

enter image description here

enter image description here

1
  • This is wonderful. It did the trick without using any additional rows. Thank you very much!
    – Mathlight
    Commented Oct 17, 2019 at 5:57
1

Generally it's difficult to work with non-continuous Excel formulas.

I suggest to add a helper row where you indicate which columns you want to summarise, then you can use this relatively simple formula:

=SUMIF($Q$4:$Y$4,1, INDEX($Q$6:$Y$19,MATCH(M6,$P$6:$P$19,0),0))

enter image description here

5
  • So you're saying that first I should create a "total sum" of the columns (P,R,T,V and X), and than use that to transport the value to the __data worksheet? There isn't an another way?
    – Mathlight
    Commented Oct 16, 2019 at 7:42
  • No, it don't need to be on the same worksheet, you can already do it in your another worksheet. I've just placed them in the same worksheet to make easier creating the screenshot. Commented Oct 16, 2019 at 7:45
  • Yes, I understand that. This was already my backup plan, but I was hoping that Excel could handle this case without additional columns..
    – Mathlight
    Commented Oct 16, 2019 at 7:49
  • 'So you're saying that first I should create a "total sum" ' - no, I haven't said that. "I was hoping that Excel could handle this case without additional columns" - you don't need additional column, do you mean row? I'm confused about what's your real requirement and what's your issue with my solution. Commented Oct 16, 2019 at 7:55
  • 1
    ow, wait.. I didn't fully understood your answer. I did not correctly see row 4 in your answer as the "selection" row.. I think I can work something with something like that.. Thank you! (will leave this question open for possible other solutions, but will get back to accept this if it's still the best)
    – Mathlight
    Commented Oct 16, 2019 at 9:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .