COUNTIFS with cell ranges

**1.zer0** · 10-01-2010, 04:05 AM

I have this formula that I botched up and it's not working how I want. I don't know what the problem is though?

=COUNTIFS(INDIRECT("'"&TEXT((B$3:B22),"dd-mm-yy")&"'!E:E"),"Yes",INDIRECT("'"&TEXT((B$3:B22),"dd-mm-yy")&"'!F:F"),"No")

Based on the number it is giving me and what I actually want I think I've deduced it to the cell ranges of (B$3:B22)

The end result I want is to give me the amount of combined "Yes's" in one column and "No's" in another where they are matched, for multiple sheets.

So if:

Row1 Yes No. This would be counted.
Row2 Yes Yes. This wouldn't be counted.
Row 3 No Yes. This is an impossibility and so is irrelevant.
Row 4 No No or "". This wouldn't be counted.

I have uploaded the file to help clarity.

Many Thanks

**peterjuhnke** · 10-01-2010, 04:26 AM

hello zer0
where are the "yes's" and "no's"
Row1 Yes No. This would be counted. WHICH TAB
Row2 Yes Yes. This wouldn't be counted.WHICH TAB
Row 3 No Yes. This is an impossibility and so is irrelevant.WHICH TAB
Row 4 No No or "". This wouldn't be counted.WHICH TAB
Regards
Peter

**1.zer0** · 10-01-2010, 05:14 AM

Hi Peter,

Someone else did part of the formula for me. I assume the part ("'"&TEXT((B$3:B22),"dd-mm-yy") designates the tab?

I have each week listed in a column which the above code ('converts'?) to the tab name and takes the data from it.

So as I understand, in English it would be (or I want it to be):

=COUNTIFS Count the number of occurences(INDIRECT("'"&TEXT((B$3:B22),"dd-mm-yy")where the sheet name is the date in the B column equal to the row number&"'!E:E"column E within that sheet),"Yes"equals yes,INDIRECT("'"&TEXT((B$3:B22),"dd-mm-yy")&"'!F:F"and also column F),"No"equals no)

that may be a really poor way of explaining it. I think the root of the problem may be having all the weeks as individual sheets?

thanks,
David

**DonkeyOte** · 10-01-2010, 05:16 AM

Originally Posted by 1.zero

I have this formula that I botched up and it's not working how I want. I don't know what the problem is though?

From what I can tell your formula returns the expected results.

Perhaps you could outline what you believe the results should be ?

**1.zer0** · 10-01-2010, 05:21 AM

The column the formula is in (titled 'Order Book') should show me how many jobs and the value of the jobs we have at that given week.

If on the week sheets the job information is 'Yes' the job was won, and 'No' the job is not complete then that would be included in the result. If the job wasn't won it is not included obviously and if the job is complete then it would be excluded from the current order book.

I just want it to show what level of work we are currently undertaking and the value of that work.

Hope that clears it up a bit better?

thanks,
David

**DonkeyOte** · 10-01-2010, 05:37 AM

David, I understand what you're trying to do - what I said was I think the results you have are correct based on your data.

If you think otherwise please outline what you believe the results should be.

**1.zer0** · 10-01-2010, 05:44 AM

Ahh yes you are right the result is correct but I would like it slightly different.

I need the result to be cumulative so that it includes the current week plus all past weeks, not just the current week.

The range I put in here: =COUNTIFS(INDIRECT("'"&TEXT((B$3:B22),"dd-mm-yy")&"'!E:E"),"Yes",INDIRECT("'"&TEXT((B$3:B22),"dd-mm-yy")&"'!F:F"),"No") I thought would do that but it is just using the first cell (B3) instead of the range.

**DonkeyOte** · 10-01-2010, 05:48 AM

Originally Posted by 1.zero

I need the result to be cumulative

Why not simply add the prior cell to the current result ?

Please Login or Register  to view this content.

**1.zer0** · 10-01-2010, 06:43 AM

Something doesn't seem right about that?

It isn't adding the previous cell to the new one properly.

The first cell gives a result of 1. Then the next cell itself is 0 so the result should be previous (1) + new cell (0) = 1. The actual result it is giving is 0?

IGNORE THIS IT WAS MY ERROR