Sumproduct, Month, Ignore text

**jennyaccord** · 06-07-2012, 11:20 AM

I've seen various solutions to problems similar to mine, however, as is always the way, my problem is everso slightly different.

I have a sumproduct formula that looks like this:

Please Login or Register  to view this content.

It works well and does what I ask of it.

Now, my problem is that I want to add in a section that deals with column A in the same manner as the formula deals with column B (i.e. MONTH(Hours!A18:A105)=4) which should work fine, except column A also contains text that is throwing off the formula resulting in a #Value result. I've seen solutions that deal with things like If(Istext(???)... but the Month function seems to be causing me a bit of an issue.

Any thoughts?

**tigeravatar** · 06-07-2012, 11:49 AM

can you provide some examples representative of what is in column A and what the calculation should include/exclude?

**Marcol** · 06-07-2012, 12:17 PM

Not sure What your end goal is?

Try either of these array formulae ...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter

**jennyaccord** · 06-07-2012, 12:22 PM

Thanks for the reply Tigeravatar (and you too Marcol. You replied whilst I was sorting out my post).

The formula is for part of a complicated booking system:

Columns A&B contain the From and To dates, with the dates formatted dd/mm/yyyy.
Column C contains one of 4, 2 letter codes denoting where the equipment has been taken from.
Column G contains the length of work time, in hours, you will have the piece of equipment (with a work day being 7.5hrs).

What I'm looking to do is calculate how many work hours a piece of equipment has been out of a locker in any given month.

For example, on entry the information is set up like this:

Please Login or Register  to view this content.

The sheet has been set up so that new entries are added into the next available empty row but, at the click of a button, they are organised in date order under the correct headings, like this:

Please Login or Register  to view this content.

What I would like to be able to do is, using the sumproduct formula, pull out only those items from the front locker (FL) that were removed in April (so it would ignore the January ones). I need to use both columns A&B as some dates may straddle a month. If an item was removed for 10 days starting on 30th April, as far as our records are concerned, the days it was out of the locker in May, still count as an April hire, if you catch my drift (it's engineering logic I think).

If I pull the information into a blank sheet and take out the headings in column A, the sumproduct formula I mentioned initially works fine. It's the headings that are throwing the formula off and I can't remove them. And, given the way the sheet is set up, the headings move whenever new information is added so I can't even write the formula to ignore certain cells. I just can't work out how to write the formula to get it to ignore text but still look for April (i.e. Month(A18:A105)=4).

Hope that makes sense.

**tigeravatar** · 06-07-2012, 12:33 PM

jennyaccord,

Attached is an example file based on the criteria you described. I'm still not sure what you want the output to be, but the workbook includes both of the formulas Marcol suggested, and they both result in 6.875 with your provided sample data. Is that what you're looking for?

**Marcol** · 06-07-2012, 12:36 PM

Does the first formula I offered give you the result you need?

Assuming that your headers are in row 17, and not row 18

[EDIT]
Never saw your reply Tiger, browser delay in refreshing seems to be a problem again.

**Marcol** · 06-07-2012, 01:10 PM

I have a feeling that the last part of the formula should be ...
*Hours!G18:G105/24
not
*SUM(Hours!G18:G105/24)

Resulting in 30/24 = 1.25 days

But I'm not sure of the logic of dividing the hours by 24 to get days in this case ...

**jennyaccord** · 06-14-2012, 06:30 AM

Thanks guys, your help has been invaluable. The formula Marcol gave works brilliantly.

Sorry for the late reply, I've been out of the office for days.

Thanks again. It's helpful advice like this that keeps me coming back here.