SUMIF/SUMPRODUCT across multiple tables question

[alfredmaul]

Hi,

I am close to my answer but, despite searching, keep missing the mark. I think it is an easy fix and appreciate the help. The gist is: I can't figure out the syntax for SUMPRODUCT to work across multiple tables if some of that data contains text. I would prefer to keep the answer as simple and readable as possible. I likely just need to rewrite the SUMPRODUCT to include those "--" things.

In the screenshot from this link -- https://i.imgur.com/TbfCmJN.png -- you can see there are multiple tables for each month. Sometimes the data will have information for column 2, 3, etc, and sometimes not. But I need to retain the ability to sort it independently within each month.

• =SUMPRODUCT((C3:I45=P3)*F3:F45) works across multiple tables but when it encounters the text in F33, it messes up with a #VALUE ! error. How do I use those "--" things or IFERROR to ignore it? I am very close...

• =SUMIF(C3:H58,P8,F3:F58) will ignore the F33 error, but it only lets me search/input text from column C. Is there a way to expand the SUMIF to look for the "P8" across more than one column? SUMPRODUCT formula finds "wages" and gives me the total from but SUMIF does not.

It's something like =SUMPRODUCT(--(C3:I45=P3),F3:F45) right, or throwing the -- before F3:F45?


Thanks!

 

[Aryatect]

Hi, your this formula should work =SUMPRODUCT(--(C3:C45=P3),F3:F45) with changing I45 to C45.

 

[alfredmaul]

Hi, thanks for the response Aryatect.

Unfortunately, changing I45 to C45 reduces the range to search for a user's input that I need. For example -- you can refer to the screenshot -- if someone were to type "car", they would get the correct amount of $2107.28 because of the SUMPRODUCT matching H19:H21 with F19:F21 in table one. I need it to accomplish this but include multiple tables. the text in column headers kills it thought.

SUMIF ignores the text but does not allow excel to search for the input beyond one column. SUMPRODUCT lets me search in an expanded range but messes up when it encounters text. Is it simply a matter of moving the "--" to somewhere before the bold part of =SUMPRODUCT((C3:I45=P3)*F3:F45) since that is the range which will have the errors?

I suppose I could stack sumifs somehow but that is not ideal... Sorry, I am trying to keep this clear but perhaps failing.

 

[Marcelo Branco]

See if this example with a small data sample helps

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Info1​	Info2​	Info3​	Amount​	Info4​		INPUT​	Result​
2​		ado​	ado​	10​			ado​	46​
3​				12​	ado​
4​	xxx​			14​
5​
6​	Info1​	Info2​	Info3​	NOOO​
7​			ado​	10​
8​		zzz​		12​
9​	ado​		yyy​	14​

Criteria in G2

Formula in H2
=SUMPRODUCT(--((A2:A9=G2)+(B2:B9=G2)+(C2:C9=G2)+(E2:E9=G2)>0),D2:D9)

M.

 

[Fluff]

The arrays in sumproduct need to be the same size, try
=SUMPRODUCT(SIGN((C3:C45=P3)+(D3:D45=P3)+(E3:E45=P3)+(G3:G45=P3)),F3:F45)
just expand it to suit

 

[alfredmaul]

Hi Marcelo,

That works, thanks. I thought it was simply a syntactical matter of adding those "--" before the range, but I can work with this for now.


=SUMPRODUCT(--((A2:A9=G2)+(B2:B9=G2)+(C2:C9=G2)+(E2:E9=G2)>0),D2:D9)

I guess I don't immediately grasp the logic of it, maybe my brain is just fuzzy from messing with it for too long. The "--" applies to D2:D9 and assigns a negative / 0 / ignore value to anything with text, right? And I don't see why the >0 is necessary or what it protects against? Sorry.

Thanks for the help either way!

 

[Marcelo Branco]

>0 is necessary because in my example row2 has two instances of "ado". It avoids double counting of this row.

See the formula suggested by Fluff - it also works and doesn't require >0

M.

 

[alfredmaul]

>0 is necessary because in my example row2 has two instances of "ado". It avoids double counting of this row.

See the formula suggested by Fluff - it also works and doesn't require >0

M.

Ahhh,

Thanks to everyone for their help.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.