SUMIF/SUMPRODUCT across multiple tables question

alfredmaul

New Member
Joined
Jul 17, 2019
Messages
4
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!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, your this formula should work =SUMPRODUCT(--(C3:C45=P3),F3:F45) with changing I45 to C45.
 

alfredmaul

New Member
Joined
Jul 17, 2019
Messages
4
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,538
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 17, 2019
Messages
4
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
>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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,134
Messages
5,484,935
Members
407,475
Latest member
Dix_Fix

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top