Excel '03 Formula to count items in 2 columns

Closed
VirtualInsanity - Jun 19, 2012 at 05:58 PM
 VirtualInsanity - Jun 27, 2012 at 06:03 PM
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

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 19, 2012 at 09:55 PM
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)))
0
VirtualInsanity
Jun 20, 2012 at 03:47 PM
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?
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 20, 2012 at 08:33 PM
=SUMPRODUCT((G1:G8="S")*(ISNUMBER(K1:K8)))
0
VirtualInsanity
Jun 21, 2012 at 08:54 PM
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?
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 21, 2012 at 10:34 PM
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.
0
VirtualInsanity
Jun 24, 2012 at 07:39 PM
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?
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 24, 2012 at 10:17 PM
what is that "C".
if you want for blanks you use ""



=SUMPRODUCT((Current!G$2:G$5000="")*(ISBLANK(Current!N$2:N$5000)))
0
VirtualInsanity
Jun 25, 2012 at 05:23 PM
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.)
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
808
Jun 26, 2012 at 04:12 AM
sorry not clear give a small example
0
VirtualInsanity
Jun 26, 2012 at 04:18 PM
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
0
VirtualInsanity
Jun 26, 2012 at 04:21 PM
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)
0
VirtualInsanity
Jun 27, 2012 at 06:03 PM
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! :)
0