Excel '03 Formula to count items in 2 columns [Closed]

Report
-
 VirtualInsanity -
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!


12 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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)))

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?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
=SUMPRODUCT((G1:G8="S")*(ISNUMBER(K1:K8)))

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?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
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.

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?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
what is that "C".
if you want for blanks you use ""



=SUMPRODUCT((Current!G$2:G$5000="")*(ISBLANK(Current!N$2:N$5000)))

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.)
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
sorry not clear give a small example

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

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)

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! :)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!