OT: MS Excel Formulas

Off topic here, but I know many of us use excel quite a bit, so I thought I'd give it a try:

I've been playing with MS Excel for awhile now, trying to learn the many formulas/functions/syntax/etc. I cannot seem to iron out a specific formula between 3 cells. In Googling my issue, I've learned a lot about other functions, just not what I'm looking for.

Basically, I want to start with a number (in cell D47), subtract from that number in the range of B3 through B30 if it contains any variation of the text in another cell, but only subtract the cell if the corresponding cell in A3 through A30 are less than or equal to 9 months old. If A3 has a date within 9 months, and B3 has the correct text, subtract 1 from D47. If only 1 of those is true, subtract nothing.

My current formula is(trying to handle one row at a time):

=D47-COUNTIFS(B9,"*"&A47&"*",A9,MONTH(TODAY()<=9))

It doesn't work, I think my syntax is off or something. Any ideas/suggestions?

=D47-COUNTIFS(B9,"*"&A47&"*",A9,MONTH(TODAY()<=9))

This should read:
=D47-COUNTIF(B9,"*"&A47&"*",A9,MONTH(TODAY()<=9))
The countifs would give you an invalid name error.

Regards,

One suggestion is to break the formula into different simpler functions.
Example:
You want a date that is 9 months sooner. Use the following in a separate cell:
=DATE(YEAR(NOW()),MONTH(NOW())-9,DAY(NOW()))

Just a thought
Regards,

I think you are missing an = before your string to compare to.

Do you mean something like:


This is an array formula, so once you have typed this in, you have to press Ctrl+Shift+Enter

Options:
- get rid of dollar signs if you are copying to other rows and want the data to move
- replace SEARCH() with FIND() for case sensitive searching

Thanks gclshortt for the 9 month formula

Huh, that countifs thing is cool, I have never used it before.


works for me.

gclshortt said:

=D47-COUNTIFS(B9,"*"&A47&"*",A9,MONTH(TODAY()<=9))

This should read:
=D47-COUNTIF(B9,"*"&A47&"*",A9,MONTH(TODAY()<=9))
The countifs would give you an invalid name error.

Regards,

Click to expand...

I tried that formula, and Excel gave me this message. I swear I'm missing something simple here; that's why I think my syntax is off. It looks to me like all of the right arguments are here, just not in the right order. I was trying to divide each row up, so it compares A3 to B3, if true subract 1, then move on to A4 & B4, etc. I thought the COUNTIFS would be the way to go since you have more than 1 criteria that you are considering for a true condition. If I use COUNTIFS in that formula, my result is a 3, but I know I am looking for a 2.


I have seen the "$" used in formulas on some of the forums I have perused, but have not seen the purpose it serves as a placeholder...what is it for? I don't know if it matters after 2010, but the version I am using is 2017. I know 2010 had some significant changes made under the hood...

Can you post a picture of the Excel spread sheet? We can then see the cells that are being called.
I would remove the (MONTH(TODAY()<=9)) and reference a cell with the information. This will simplify the expression for troubleshooting.

Regards,

I agree with Garry and suggest taking it one or two steps further. When I am working out a relatively complex formula like this, I like to break out each individual piece and combine them at the end. This lets you see exactly where the problem is, visually, without trying to drill-down.

You can always combine into a single formula later, if you want to. I often find I like much of the broken-out version if it's my own working spreadsheet rather than a presentation of some kind.

The $ locks either the column or row reference and is useful when copying formulas.

For example, $L32, if copied and pasted to the right would be $L32 in all the pasted cells. If copied and pasted down would be $L33, $L34, $L35 and so on. Because the $ is written before the column reference (L), it locks the column.

Similarly, L$32, if copied and pasted to the right would be M$32, N$32, O$32 and so on. If copied and pasted down would be L$32 in all the pasted cells. Once again, because the $ no precedes the row reference (32), the row reference is locked.

Finally, $L$32 locks both the colum and row. Copying to any cell will be $L$32.

gclshortt said:

Can you post a picture of the Excel spread sheet? We can then see the cells that are being called.
I would remove the (MONTH(TODAY()<=9)) and reference a cell with the information. This will simplify the expression for troubleshooting.

Regards,

Click to expand...

I'll do you one better....


It's a fairly simple spreadsheet for keeping track of time off. Just trying to keep a current count of when days expire/renew and when they aren't relevant anymore. More-so teaching myself how to use formulas and functions.

Steve Etter said:

The $ locks either the column or row reference and is useful when copying formulas.

For example, $L32, if copied and pasted to the right would be $L32 in all the pasted cells. If copied and pasted down would be $L33, $L34, $L35 and so on. Because the $ is written before the column reference (L), it locks the column.

Similarly, L$32, if copied and pasted to the right would be M$32, N$32, O$32 and so on. If copied and pasted down would be L$32 in all the pasted cells. Once again, because the $ no precedes the row reference (32), the row reference is locked.

Finally, $L$32 locks both the colum and row. Copying to any cell will be $L$32.

Click to expand...

Makes sense. I like.

I have split out the date - 9 months in cell A32

Here is what the formula now looks like:
=D47-COUNTIFS($B$5:$B$30,"*"&A47&"*",$A$5:$A$30, ">="&$A$32)

Attached is the file.

Regards,

Yahtzee!

So I was finally able to make it work using the tips you guys gave me...I learned something new.

It isnt't quite as dynamic as I would like when comparing 30 different sets of cells (as I had to copy it over and over again only changing the cells referenced), but it works. I even got to use my newly learned "$" tip.

Thank you all for your help!