# Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)

Printable View

Show 40 post(s) from this thread on one page
Page 2 of 2 First 12
• Mar 16th, 2011, 05:38 PM
Cease
Re: Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)
Purple_Girl,

Glad it works so far :D

to match it to a category, the most straightforward way would be to add a criteria range and criteria to the countifs function.

If names are in column a, ttl errors are in b, and category is c: throw this in d2:

=countifs(A:A,a2,B:B,">0",C:C,c2)
or instead of "c2":
=countifs(A:A,a2,B:B,">0",C:C,"Category Name")

Hope it helps :D
• Mar 16th, 2011, 05:57 PM
Purple_Girl
Re: Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)
THANK YOU SO MUCH!!! You are a HUGE help!!! This works GREAT for my project!!! Many thanks to you!!! :)
• Mar 16th, 2011, 06:00 PM
Cease
Re: Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)
Anytime,

As much great info as I've gotten here, I'm glad I can return the favor for a change.

Cheers :nya:
• Mar 17th, 2011, 01:33 PM
Purple_Girl
Re: Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)
@ Cease (or anyone out there): Oh - another question!! I've now come across the fact that I am going to need to add a condition to one of the references in my formula:

=IF(\$W15=0," ",COUNTIFS(\$G:\$G,\$W15,\$C:\$C,">0",\$I:\$I,\$AB\$8,\$A:\$A,\$AM\$5))

W15 is the Name that I want to find
Col G is the list of names
Col C is the list of TTL Errors values
Col I is the list of Categories
AB8 is the Category name that I want to find
Col A is the list of months
AM5 is the month that you select from the drop down list...so, all months are listed and the formula works great when a month is chosen, BUT when "All" is chosen in the drop down, the formula does not know what to do.

My question is, how do I write the formula to include the condition that if "All" is selected, it must still calculate the values??

Thanks to all who respond!!!!
:)
• Mar 17th, 2011, 03:05 PM
Purple_Girl
Re: Which formula - VLOOKUP, MATCH, INDEX??! (Excel 2007)
All, I've figured it out! Here's my new formula:

=IF(\$W14=0," ",IF(\$AM\$5="(ALL)",COUNTIFS(\$G:\$G,\$W15,\$C:\$C,">0",\$I:\$I,\$AB\$8),COUNTIFS(\$G:\$G,\$W15,\$C:\$C,">0",\$I:\$I,\$AB\$8,\$A:\$A,\$AM\$ 5)))

:biggrin:
Show 40 post(s) from this thread on one page
Page 2 of 2 First 12