Sumif with a defined named table?

jwillits

New Member
Joined
Jul 10, 2018
Messages
36
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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
Excel 2010
BCDEF
1503030KNOWN_REVENUE
2APPLES20APPLES
3PEARS10PEARS
4GRAPES30ORANGES
5ORANGES20
6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
5b

Worksheet Formulas
CellFormula
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"}))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
KNOWN_REVENUE='5b'!$F$2:$F$4

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
5b

Worksheet Formulas
CellFormula
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"))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
KNOWN_REVENUE='5b'!$M$2:$M$4

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>


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

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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