help to make formula dynamic

[Lewster]

Hello all, I would like help to make these formula's dynamic so they do not return an error when there are empty cells but include new data when added.

=SUMPRODUCT((Plant=$H15)*(Month=$I$13)*(Year=$J$13))

=SUMPRODUCT((Plant=$H15)*(Month=$I$13)*(Year=$J$13)*(Alarms))

Thanks

Lewster

[NBVC]

Unless the ranges are of different sizes or you have errors within the data or the Alarms range contains text instead of numbers, I don't really see errors coming up.

[Pete_UK]

You can change the formulae to these:

=COUNTIFS(Plant,$H15,Month,$I$13,Year,$J$13)

and:

=SUMIFS(Alarms,Plant,$H15,Month,$I$13,Year,$J$13)

and then you can define the named ranges Plant, Month, Year and Alarms as full-column references, so that they will include any new data that you add.

Hope this helps.

Pete

[Lewster]

Hi, the plant and month are text, year is YYYY and alarms are numbers.
Lewster

[Lewster]

You can change the formulae to these:

=COUNTIFS(Plant,$H15,Month,$I$13,Year,$J$13)

and:

=SUMIFS(Alarms,Plant,$H15,Month,$I$13,Year,$J$13)

and then you can define the named ranges Plant, Month, Year and Alarms as full-column references, so that they will include any new data that you add.

Hope this helps.

Pete

Thanks Pete, will these work if data is being pulled from a different workbook without it being open?

Lewster

[Pete_UK]

So you mean those named ranges are in a separate (closed) workbook? There was no hint in your original formulae that the data was external.

You will probably have to include the full path, filename and sheetname to the ranges.

Hope this helps.

Pete

[NBVC]

Hi, the plant and month are text, year is YYYY and alarms are numbers.
Lewster

So what errors are you getting? Again, if your ranges are equal size (with blanks or not) then it should not give errors (I think).

Countifs and Sumifs don't work when referencing closed workbookis.

[Lewster]

So you mean those named ranges are in a separate (closed) workbook? There was no hint in your original formulae that the data was external.

You will probably have to include the full path, filename and sheetname to the ranges.

Hope this helps.

Pete

Sorry, I was just using these as a sample as I was aware that I would need full address to the workbook and range, I was just checking that they would work on a closed workbook as I think I am right in saying some formula need the workbook to be open to work.
Thanks very much for your help
Lewster

[Lewster]

So what errors are you getting? Again, if your ranges are equal size (with blanks or not) then it should not give errors (I think).

Countifs and Sumifs don't work when referencing closed workbookis.

I am wanting to count the number of times a certain plant name occurs in a month in year in the first formula
In the second I want to count the number of alarms by plant, month, year
All works well for Jan, Feb, Mar, Apr but for May I get no count at all even though there are entries in for May, the ranges are even and are only at row 200 from a range of 500.
Lewster

[Lewster]

I asked for the same help with this formula
=SUMPRODUCT((Test.xlsx!Plant=$F$2)*(Test.xlsx!Condition=$D5)*(MONTH(Test.xlsx!Date)=MONTH($C$2)))

and got this which works well
=SUM(IF(Test.xlsx!Plant=$F$2,IF(Test.xlsx!Condition=$D5,IF(ISNUMBER(Test.xlsx!Date),IF(MONTH(Test.xlsx!Date)=MONTH($C$2),1)))))

so something along the same line is what I am looking for help to achieve, I just cannot figure out how to adapt my above formulae to achieve the same result.
Lewster

[Pete_UK]

Well, that formula implies that the workbook Test.xlsx is open, otherwise you would need the full path etc. Please try to be consistent with your requirements.

Pete

[Tony Valko]

I asked for the same help with this formula
=SUMPRODUCT((Test.xlsx!Plant=$F$2)*(Test.xlsx!Condition=$D5)*(MONTH(Test.xlsx!Date)=MONTH($C$2)))

and got this which works well
=SUM(IF(Test.xlsx!Plant=$F$2,IF(Test.xlsx!Condition=$D5,IF(ISNUMBER(Test.xlsx!Date),IF(MONTH(Test.xlsx!Date)=MONTH($C$2),1)))))

I suggested the array formula because the range Test.xlsx!Date had some text entries which caused the MONTH(...) function to error.

[Lewster]

It was open when I inserted the formula so that it took care of all the path addresses itself but obviously when closed it had the full path address in the formula on Tony's advice.
Lewster

[Lewster]

Still looking for help

[dflak]

Can you attach the two sample workbooks?

[Tony Valko]

What kind of errors do you get?

[Lewster]

What kind of errors do you get?

Hi Tony,
I am wanting to count the number of times a certain plant name occurs in a month in year in the first formula
In the second I want to count the number of alarms by plant, month, year
All works well for Jan, Feb, Mar, Apr but for May I get no count at all even though there are entries in for May, the ranges are even and are only at row 200 from a range of 500.
Lewster

[Tony Valko]

I get no count at all even though there are entries in for May, the ranges are even and are only at row 200 from a range of 500.

Does that mean you don't even get a result of 0? Just a blank cell?

[Lewster]

It actually has a dash in it if no result but never 0

[Tony Valko]

A dash is an accounting format for 0.

Well, it's kind of difficult to make suggestions without seeing the files to be able to troubleshoot.

Are you able to post the files?

[Lewster]

I am afraid not they are on works network and sensitive, I will play around some more and see if i can figure it out.

Move on and help someone else Tony, thanks for continuing to help.

Thanks to all for the help given.

Lewster

[Tony Valko]

Here are a couple of common problems to look for...

Numbers entered/formatted as text. By default numeric numbers are aligned to the right. If you see numbers aligned to the left that's not normal and should be checked into.

Unseen whitespace characters in the data.

The underscores represent space characters.

North_
_North
_North_

You may have a formula that calculates based on "North". If there are whitespace characters obviously they don't match "North" and will cause unexpected results.

[dflak]

Along with Tony's suggestion. Check the month values. Change the format of the columns containing the dates to number. Real dates will convert to a number. Strings will stay as they are. So if you were looking for "May", it might actually be 5/1/2016 originally formatted as "mmm".

[Lewster]

Thank you both for your help, it is now solved, working on dflak's suggestion I checked the raw data and there was indeed numbers instead of text in 1 entry.
All is good now, thank you again for continued support and help to resolve my issue, you are both gentlemen.

Lewster

[Tony Valko]

Good deal. Thanks for the feedback!