COUNTIFS formula that works in the same row

**julesmck** · 05-28-2016, 11:35 AM

HI, I am trying to get a COUNTIFS formula to work. I'm using Excel 2013.

I have two worksheets: one called 'TOTALS' and one called 'INVENTORY.'

I want a formula on the TOTALS worksheet that gets its data from the INVENTORY worksheet.

Specifically, I want to insert a formula in the TOTALS worksheet that looks for variations on the term "Advocates" in cells I5:I350 in the INVENTORY worksheet. If the term "Advocate" is found, I want the formula to examine the contents of column G in the same row. If the term "Current" appears in column G, the formula should count it as one instance.

Here's the formula I'm trying to get to work:

=COUNTIFS(INVENTORY!I5:I350,"*" & "Advocates" & "*",INVENTORY!G5:G350,"Current")

Any insight would be appreciated. Thank you!

**FDibbins** · 05-28-2016, 11:44 AM

Hi, welcome to the forum

Hard to say without seeing a sample of what you are working with.

That should work...
=COUNTIFS(INVENTORY!I5:I350,"*Advocates*",INVENTORY!G5:G350,"Current")

Can you actually see entries that should be counted?
Check for spelling errors with Current?

If all else fails, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

	O	P	Q
1	aabb	xx	1
2	bbaacc	yy
3	ccbb	xx
4	bbaacc	yy
5	ccbb	xx
6	bbccaa	yy

Q1=COUNTIFS(O1:O6,"*aa*",P1:P6,"xx")

**julesmck** · 05-28-2016, 12:41 PM

Thanks so much, Ford.

I've uploaded the workbook here. The cell that contains the formula I'm trying to get to work is in yellow.

**FDibbins** · 05-28-2016, 12:47 PM

Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

**julesmck** · 05-28-2016, 12:55 PM

Apologies - I didn't realize you could attach files to a post. Here it is (I hope). And thanks again!

**FDibbins** · 05-28-2016, 01:12 PM

The "current" you have in G has a trailing space, and it seems you have a macro running, that when I remove the space, it adds the word back (and back again, even if you just type the word 'current"), The only way I found to fix that was to delete the entry and use the DD again.

OH and btw, this formula should make it easier to copy what you want...
=COUNTIFS(INVENTORY!$I$5:$I$350,"*" &$A3 & "*",INVENTORY!$G$5:$G$350,B$2)

**julesmck** · 05-28-2016, 04:13 PM

D'oh -I can't believe I didn't catch that trailing space. The formulas now seem to work properly and I can't thank you enough, but I'll try: thank you!

**FDibbins** · 05-29-2016, 12:36 AM

Thats OK, it took me a bit to find it too, Im just happy we were able to resolve this for you

, so thanks for the feedback

COUNTIFS formula that works in the same row

LinkBack

Thread Tools

Rate This Thread

Display

COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Re: COUNTIFS formula that works in the same row

Thread Information

Users Browsing this Thread

Similar Threads

Formula works for all data in table, but want to update formula to calculate per filter

[SOLVED] Sumifs formula giving #value even though each part individually works as a sum formula

how can i make this code works for multiple links it only works for one link

[SOLVED] weekday() result used in an array formula doesn't work. Formula works if i type in date

COUNTIFS works on single date, but fails to count between date range

Excel 2007 : More efficient: 2 long COUNTIFS or 1 COUNTIFS and 1 VLOOKUP based on the COUNTIFS?

Formula works, but works too well...

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions