can someone please translate this for me

[Just Me]

I'm trying to create my own credit card analyzer and I saw one close to what
I want and it had a worksheet that had all the merchants listed in column
"A" this workbook had 3 worksheets in it, the main one was where the credit
card data is entered. in the formula column it had this
=IF(COUNTIF(StoreList,B2),B2,"Other") I want to understand how this formula
tells the main worksheet to use data from the merchant worksheet.

Thanks

 

[Tyro]

All that formula is doing is counting the number of times the value in B2
occurs in the named range StoreList. If the count is greater than zero, it
returns the value in cell B2. If the count is zero, it returns "Other". Some
other formula may be using the result the formula returns to use data from
the merchant worksheet.

 

[JMB]

If B2 appears in the named range Storelist, the formula will return whatever
is in B2. Otherwise, it returns the word "Other".

Normally, people are accustomed to seeing some conditional test as the first
argument for an IF statement. Note that XL stores FALSE as 0 and TRUE as 1.

So if the countif function returns 0, IF will process this as a false
condition. Anything else (positive or negative numbers) will be processed as
a true condition.

IF(COUNTIF(StoreList,B2), true condition, false condition)
is the same as
IF(COUNTIF(StoreList,B2)>0, true condition, false condition)

 

[T. Valko]

The formula is testing the value of cell B2 to see if it is present in the
named range StoreList. This sounds like a list of store names. If the value
in cell B2 is present in StoreList then the formula returns the value of
cell B2. If the value in cell B2 is not present in StoreList the formula
returns the word "other".

The logic of the formula is that the COUNTIF function will return the total
count of cell B2 within the named range StoreList. If the count is 0,
meaning the value of cell B2 is not present in StoreList, this evaluates to
FALSE and the IF function retunrs the value_if_false argument which is
"other". If the count of cell B2 within the named range StoreList is *any*
number other than 0, meaning the value of cell B2 is present in StoreList,
then the IF function evaluates this to be TRUE and the IF function returns
the value_if_true argument which the value of cell B2.

 

[Just Me]

WOW thanks for all the very detailed explanations, I was wondering what in
the formula called data from another worksheet. Is it the word "Storelist" ?
the tab on the worksheet is named "Stores"

What I am trying to do is create a credit card analyzer that will allow me
to paste my downloaded CSV data from my credit card and paste it into a
worksheet. Then have some kind of formula that will tell me the totals for
each merchant. I would like to have the list of merchants on one worksheet
and have the data that I paste on another. Now you can see why I was trying
to understand what exactly the formula was saying.

In the one I want to create I have 3 columns "A" has the date, "B" has the
dollar amount, "C" has the merchant I would like my formula to give me the
merchant in "D" and the total amount that was spent at that merchant in "E"


If anyone knows of a workbook that already does this I would appreciate if
you could tell me where I could get it. Also if anyone has any ideas on how
I can create this workbook I am all ears.

Thanks again

 

[JMB]

Storelist is a named range. Try clicking on the drop arrow of the name box
in the upper left corner and see if Storelist appears in there. If so,
select it and it should take you to it. Or, the range could be defined by
some other formula. You should be able to click on Insert/Names/Define (XL
versions prior to 2007) and be able to see the details of how it is defined.