# Sum multiple rows on single condition

Suppose I have the following table in sheet month:

And the following table in sheet __data:

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);"")  • 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? Commented Oct 16, 2019 at 9:50 ## 2 Answers 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}))))  • This is wonderful. It did the trick without using any additional rows. Thank you very much! Commented Oct 17, 2019 at 5:57 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))`

• 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? 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.. 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
• 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) Commented Oct 16, 2019 at 9:54