Sumif with a defined named table?

J

jwillits

New Member
Joined
Jul 10, 2018
Messages
38
Office Version
  1. 2010
Platform
  1. Windows
All I am trying to say is I want the sum of column D if no value in my defined named revenue (KNOWN_REVENUE) matches anything in column C.

This is probably way off.

=SUMIFS(D:D,C:C,<>KNOWN_REVENUE)

Here is an example.

KNOWN_REVENUE
APPLES
PEARS
ORANGES

column C column D
APPLES 20
PEARS 10
GRAPES 30
ORANGES 20

And the formula would produce the value 30, because grapes is not listed in my defined named table.
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes

Excel 2010
CDEF
130KNOWN_REVENUE
2APPLES20APPLES
3PEARS10PEARS
4GRAPES30ORANGES
5ORANGES20
6
5b
Cell Formulas
RangeFormula
D1=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),D2:D5)
Named Ranges
NameRefers ToCells
KNOWN_REVENUE='5b'!$F$2:$F$4
 
Upvote 0

Excel 2010
BCDEF
1503030KNOWN_REVENUE
2APPLES20APPLES
3PEARS10PEARS
4GRAPES30ORANGES
5ORANGES20
6
5b
Cell Formulas
RangeFormula
B1=SUMPRODUCT(--(ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),D2:D5)
C1=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),D2:D5)
E1=SUM(D:D)-SUM(SUMIFS(D:D,C:C,{"Apples","Pears","Oranges"}))
Named Ranges
NameRefers ToCells
KNOWN_REVENUE='5b'!$F$2:$F$4
 
Upvote 0
Dave Patton said:
Excel 2010
BCDEF
KNOWN_REVENUE
APPLESAPPLES
PEARSPEARS
GRAPESORANGES
ORANGES

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]30[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
5b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUMPRODUCT(--(ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),D2:D5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),D2:D5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUM(D:D)-SUM(SUMIFS(D:D,C:C,{"Apples","Pears","Oranges"}))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]KNOWN_REVENUE[/TH]
[TD="align: left"]='5b'!$F$2:$F$4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Looks good. One more question. If I had multiple criteria, like this, as well as (F:F,"Jun 2018") and (G:G,">0"), what would the formula look like to add all these together into one formula?
 
Upvote 0
COLUMN F IS JUST A ROW OF DATES, AND COLUMN G IS JUST A NUMBER. MY ENTIRE FORMULA IS THIS

=SUMIFS(D:D,H:H,"Jun 2018",D:D,">0",C:C,"<>KNOWN_REVENUE")

SAYING I WANT THE VALUE OF COLUMN D, IF COLUMN H IS JUN 2018, COLUMN D IS GREATER THAN 0, AND COLUMN C DOES NOT CONTAIN ANY VALUE IN (KNOWN_REVENUE).

OF COURSE THIS FORMULA DOESN'T WORK, EVERYTHING WORKS EXCEPT THE PART WHERE COLUMN C DOES NOT CONTRAIN ANY VALUE IN KNOWN REVENUE. CANT SEEM TO GET THAT TO WORK.
 
Last edited:
Upvote 0
Does your Date part work?

You may have to use the SumProduct or use a helper column.


Excel 2010
BCDEFG
1303050
231-May-18APPLES204-May-18
330-Jun-18PEARS1015-Jun-18100
4GRAPES3016-Jun-18200
5ORANGES2015-Jun-18300
6
5b
Cell Formulas
RangeFormula
B1=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),--(G2:G5>0),--(F2:F5>43251),--(F2:F5<=43281),D2:D5)
C1=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),--(G2:G5>0),--(F2:F5>B2),--(F2:F5<=B3),D2:D5)
E1=SUM(D:D)-SUM(SUMIFS(D:D,C:C,{"Apples","Pears","Oranges"},F:F,">"&B2,F:F,"<="&B3,G:G,">0"))
Named Ranges
NameRefers ToCells
KNOWN_REVENUE='5b'!$M$2:$M$4
 
Last edited:
Upvote 0
Dave Patton said:
Does your Date part work?

You may have to use the SumProduct or use a helper column.

Excel 2010
BCDEFG
APPLES
PEARS
GRAPES
ORANGES

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=F2F2F2]#F2F2F2[/URL] , align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]31-May-18[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4-May-18[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]30-Jun-18[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15-Jun-18[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16-Jun-18[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15-Jun-18[/TD]
[TD="align: right"]300[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
5b​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),--(G2:G5>0),--(F2:F5>43251),--(F2:F5<=43281),D2:D5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),--(G2:G5>0),--(F2:F5>B2),--(F2:F5<=B3),D2:D5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUM(D:D)-SUM(SUMIFS(D:D,C:C,{"Apples","Pears","Oranges"},F:F,">"&B2,F:F,"<="&B3,G:G,">0"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]KNOWN_REVENUE[/TH]
[TD="align: left"]='5b'!$M$2:$M$4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Yes, the other parts work fine, I just dont know how to add this to what I already have.

SUMIFS(D:D,H:H,"Jun 2018",D:D,">0")

This formula works with these 2 criteria, but adding this one to it is difficult.
 
Upvote 0

Excel 2010
BCDEFG
13030
231-May-18APPLES204-May-18
330-Jun-18PEARS1015-Jun-18100
4GRAPES3016-Jun-18200
5ORANGES5015-Jun-18300
6
5b
Cell Formulas
RangeFormula
B1=SUMPRODUCT(--(1-ISNUMBER(MATCH(C2:C5,KNOWN_REVENUE,0))),--(G2:G5>0),--(F2:F5>43251),--(F2:F5<=43281),D2:D5)
E1=SUMIFS(D:D,F:F,">"&43251,F:F,"<="&43281)-SUM(SUMIFS(D:D,C:C,{"Apples","Pears","Oranges"},F:F,">"&43251,F:F,"<="&43281,G:G,">0"))
Named Ranges
NameRefers ToCells
KNOWN_REVENUE='5b'!$M$2:$M$4
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
211
Fluff
Fluff
S
  • Solved
Delete Row if value in column C is a duplicate
Replies
8
Views
273
igold
igold
M
  • Question Question
SumIfs Formula
Replies
2
Views
144
FormR
FormR
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
3K
Kevin_Li
Kevin_Li
N
  • Solved
SUMIF Formula
Replies
3
Views
357
Legacy 143009
L

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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
Back
Top