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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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

 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Is there a reason the logic cannot be included in the sumifs itself? For the and or piece that is.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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