Excel '03 Formula to count items in 2 columns Closed

Question

Hello, 

I just need a little help. I'm trying to use the CountIf formula to reference two columns of data.

I've been using this formula: =COUNTIF('In Processing'!G:G,"S") to count how many occurances of 'S' are present in Column G but I need to adapt it to do the following...

Column K has a date  - if a date is present in column K I want it to check column G of that row for a specific value (as above the value is 'S'). 

The result I'm looking for is how many occurances there are of 'S' for those records that have a date in column K.

Any help is much appreciated!


Configuration: Windows XP / Internet Explorer 8.0

venkat1926 · Answer

suppose the date in col K which is relevant  is 6/2/12

then try this formula


=SUMPRODUCT((G1:G13="S")*(K1:K13=DATE(2012,6,2)))

VirtualInsanity · Answer

Thanks for your response but it's not quite what I'm looking for in that I don't want to put a specific date. The criteria for searching through column K needs to be is there any entry (any date) or nothing in this column. If there is then how many of those occurances with a date in column K also have the value 'S' in column G.

Hope that makes sense?

venkat1926 · Answer

=SUMPRODUCT((G1:G8="S")*(ISNUMBER(K1:K8)))

VirtualInsanity · Answer

This is good. Many thanks.

I neglected to mention though that the columns it's referencing are on a separate spreadsheet. I tried to adapt the formula as follows:

=SUMPRODUCT((In Processing!G:G="S")*(ISNUMBER(In Processing!K:K)))

but it comes up with  =NAME?

Tried absolute references as well but same issue.

=SUMPRODUCT((In Processing!G$2:G$5000="S")*(ISNUMBER(In Processing!K$2:K$5000)))

What am I missing here?

venkat1926 · Answer

In such cases it is better to use the mouse to delineanae the various ranges.

that in the second sheet type
=sumproduct((
take the mouse to the first sheet and drag the mouise from G1 to G10(or last cell)
and then type 
=S)*(
and then drag the mouse from K1 down in the second sheet
and type three  brackets.

anyhow the final formula is 

=SUMPRODUCT(('in processing'!$G$1:$G$10="S")*(ISNUMBER('in processing'!$K$1:$K$10)))

do you see the single quote for the sheet name that is because the sheet name is not  one word.

in writing formula al far as possible use mouse to draw the ranges.

VirtualInsanity · Answer

Great - many thanks. 

One last question. How do I get it to do the reverse and reference the rows that have blanks?

I tried this:

=SUMPRODUCT((Current!G$2:G$5000="C")*(ISBLANK(Current!N$2:N$5000)))

It accepts the formula but doesn't return the right number. I tested it and it seems to only be giving a result for the last record that matches the criteria - so only shows the result as 1 when there should be more than that. 

Do you have any suggestions?

venkat1926 · Answer

what is that "C".
if you want for blanks you use ""



=SUMPRODUCT((Current!G$2:G$5000="")*(ISBLANK(Current!N$2:N$5000)))

VirtualInsanity · Answer

Sorry I didn't explain my question fully.

I want it to look for blank cells in column N and then return a result telling me how many of those rows have 'C' in column G (i.e. doing the same thing as the other formula you gave me but instead of looking for a date it's looking for blank cells. I tried ISBLANK but that didn't work.)

venkat1926 · Answer

sorry not clear give a small example

VirtualInsanity · Answer

Hopefully this displays right. Below is the example. Dues to space constraints here the columns in question for this example are G and D. So if there is no entry in column G then I want it to count how many C's there are for those entries in column D. So the result for this example should be 2.


A               B             C              D             E               F              G   
Simon	Simmons	Officer	C	Bla	Bla	12/05/2012
James      Johnson   Admin	C	Bla	Bla	
Chris	Hart	Officer	C	Bla	Bla
Sophie	Smith	Admin	S	Bla	Bla	15/06/2013

Hope that helps

VirtualInsanity · Answer

Ok that didn't display right when posted. So just to clarify it goes like this:

Column A - Persons first name
Column B - Persons last name
Column C - Position
Column D - Classification (C or S)
Column E - Bla
Column F - Bla
Column G - Date (or no date)

VirtualInsanity · Answer

Oops - ok, ignore all of that guff. I'm being a complete muppet and forgot the column in question has a formula to give a result! Used your first code and got it working fine.

Thanks so much for you help - and patience! :)

Excel '03 Formula to count items in 2 columns

12 responses

Similar discussions

Subscribe To Our Newsletter!