Formula with "if" "and" between two values "sumifs" data from pivot table

NeilNeil

New Member
Joined
May 24, 2016
Messages
11
Hi Mr. Excel,

I am stuck on the following formula.

=IF(OR(Nmd1>="200",Nmd1<"300"),SUMIFS(A1,Nmd2,'Sheet1'!$A161),"0")

Nmd1 & Nmd2 are both named ranges that link to data in a pivot. Example as follows "=+'DATA PULL DOWN FROM SYSTEM'!B4995"

This formula works, but I need this to return the results Nmd1 => "200" and < "300". I have used "and" instead of "or", but it is not providing the results.

The intention is to use a variation of this within a larger formula, so it will appear 12 times. Happy to provide further details if needed.

Wisdom very much appreciated :)

Kind Regards,

Neil
 

Some videos you may like

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.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
the "" is looking for text and not a number in the cell
also
"0" is entering text and not zero

=IF(AND(Nmd1>=200,Nmd1<300),SUMIFS(A1,Nmd2,'Sheet1'!$A161),0)

does that work , if not
do this
=AND(Nmd1>=200,Nmd1<300)
and see if you get a TRUE or FALSE

 

NeilNeil

New Member
Joined
May 24, 2016
Messages
11
Hi Wayne,

Thank you.

=IF(AND(Nmd1>=200,Nmd1<300),SUMIFS(A1,Nmd2,'Sheet1'!$A161),0)

This is returning "0" only. There should be a figure.

=AND(Nmd1>=200,Nmd1<300)
This one is returning FALSE across the spreadsheet (regardless of whether it should be TRUE or FALSE).

Apologies, I have omitted that A1 is a named range. I have updated the formula to ACT1. I made it look like a cell reference instead of a named range when making this confidential...sorry about that...

=IF(OR(Nmd1>="200",Nmd1<"300"),SUMIFS(ACT1,Nmd2,'Sheet1'!$A161),"0")

Kind Regards,

Neil
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Mr. Excel,

I am stuck on the following formula.

=IF(OR(Nmd1>="200",Nmd1<"300"),SUMIFS(A1,Nmd2,'Sheet1'!$A161),"0")

Nmd1 & Nmd2 are both named ranges that link to data in a pivot. Example as follows "=+'DATA PULL DOWN FROM SYSTEM'!B4995"

This formula works, but I need this to return the results Nmd1 => "200" and < "300". I have used "and" instead of "or", but it is not providing the results.

The intention is to use a variation of this within a larger formula, so it will appear 12 times. Happy to provide further details if needed.

Wisdom very much appreciated :)

Kind Regards,

Neil

probably excel treated Nmd1 and Nmd2 as cells NMD1 and NMD2 rather than the named ranges.
 

NeilNeil

New Member
Joined
May 24, 2016
Messages
11

ADVERTISEMENT

Thank you AlanY - I have used tab to complete, so definitely using the named range.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,426
Office Version
  1. 365
Platform
  1. MacOS
also A1 cannot be a named range as its also a cell
I cannot use
A1, NMD1 or NMD2 as a named range without excel calling an error
 

NeilNeil

New Member
Joined
May 24, 2016
Messages
11

ADVERTISEMENT

Hi Etaf,

Please see below. I had tried to make this formula generic so the answer would benefit more people. The following is the full formula. These are all named ranges. Apologies for the confusion here.

=IF(OR(Cost_Centre>="200",Cost_Centre<"300"),SUMIFS(Actual1,Account_Number,'Operations (2xx)'!$A161),"0")

Hope this makes sense, and thank you for all of your help.

Kind Regards,

Neil
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,239
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Etaf,

Please see below. I had tried to make this formula generic so the answer would benefit more people. The following is the full formula. These are all named ranges. Apologies for the confusion here.

=IF(OR(Cost_Centre>="200",Cost_Centre<"300"),SUMIFS(Actual1,Account_Number,'Operations (2xx)'!$A161),"0")

Hope this makes sense, and thank you for all of your help.

Kind Regards,

Neil

this check, OR(Cost_Centre>="200",Cost_Centre<"300") is meaningless as it covers ALL.
 

dhsilv2

Board Regular
Joined
Jul 8, 2014
Messages
54
Is there a reason the logic cannot be included in the sumifs itself? For the and or piece that is.
 

NeilNeil

New Member
Joined
May 24, 2016
Messages
11
Hi AlanY - yes it is returning everything. I have tried to use the AND function to limit this but have not had success.

dhsilv2 - I tried the following formula as a base to add multiple criteria into the SUMIF-

SUM(SUMIFS(Actual1,Cost_Centre,{">=200","<300"})*{1,-1})

As soon as I tried to add the extra criteria I only receive 0 values.

Thank you,

Neil
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,061
Members
414,498
Latest member
jordanmiller7890

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
Top