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

[NeilNeil]

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

 

[etaf]

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]

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]

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]

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

 

[etaf]

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]

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]

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]

Is there a reason the logic cannot be included in the sumifs itself? For the and or piece that is.

 

[NeilNeil]

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