using countifs for comparing columns

[TheGod1]

Hello Everyone,
I am in a bit of a pickle. Before I came here to post a thread - I did some research and could not find a solution using COUNTIFS ; the closest I have come to is using 'sumproduct' which in my excel spreadsheet for some reason it is not working.

Basically what I have is an excel spreadsheet with a few columns - but for this forum I will simplify it and provide a smaller example.

[TABLE="width: 500"]
<tbody>[TR]
[TD]COMPLETED[/TD]
[TD]TRIAL REQUESTED[/TD]
[TD]TRIAL COMPLETED[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]10-OCT-2014[/TD]
[TD]20-OCT-2014[/TD]
[/TR]
</tbody>[/TABLE]

Above is a simplified version of the table of what I am using.
Basically I am trying to create a formula that says if Column B (trial requested) > column C (trial completed) then it needs to return a +1 for count.
I have tried the following:
=COUNTIFS(A:A,"Y",B:B>C:C)
The second part of the above formula does not work. I was wondering if anyone had a way to fix this using the countifs method. I have tried the SUMPRODUCT method already [sumproduct(--....,--.....)] etc, but for some reason it just doesn't work with my spreadsheet - maybe it's because I have about 8 different "criteria" that I am using?

Any help would be very much appreciated.
P.S Hope you all had a great Halloween!

 

[Domenic]

Try...

=SUMPRODUCT(--(A2:A100="Y"),--(B2:B100>C2:C100))

or

=SUM(IF(A2:A100="Y",IF(B2:B100>C2:C100,1)))

...confirmed with CONTROL+SHIFT+ENTER.

Adjust the ranges, accordingly.

 

[TheGod1]

Try...

=SUMPRODUCT(--(A2:A100="Y"),--(B2:B100>C2:C100))

or

=SUM(IF(A2:A100="Y",IF(B2:B100>C2:C100,1)))

...confirmed with CONTROL+SHIFT+ENTER.

Adjust the ranges, accordingly.

1) First off, thanks so much for your reply Domenic. Much appreciated.
2)As previously mentioned - i was able to get the SUMPRODUCT to work on my 'small sample' spreadsheets where I was trying out formulas. But on the actual spreadsheet itself the sumproduct formula would not work.
3) Is it possible to use the COUNTIFS statement, or will I end up having to either find a fix for why my sumproduct formula is not working?
4) Is it possible to use continuous nested 'if' statements like your second example? (note: In my real spreadsheet, I would have to use about 7 "ifs" if this is the case.

Thanks in advance!

 

[Domenic]

You say that on your actual worksheet SUMPRODUCT does not work. Does it return an error? If so, which error? Can you post the exact formula that you tried?

You also say that you'll have to use 7 conditions. Can you describe those conditions?

 

[FDibbins]

If the sumproduct wont work on the real data, are all your dates actually dates, and not text that looks like a date?

Try a test with just the 1st part, and then with just the 2nd part

 

[TheGod1]

If the sumproduct wont work on the real data, are all your dates actually dates, and not text that looks like a date?

Try a test with just the 1st part, and then with just the 2nd part

Wow, very good observation.
I didn't even notice this before!
There are in fact some columns in the '2nd column of dates' that has some text in it instead of dates.
Is there anything that can be done to modify the formula?
But I've also noticed that the previous criteria in the SUMPRODUCT formula already removes all rows that would be non-dated (in the actual formula on my spreadsheet). Or does EXCEL not process it from left to right and remove any necessary rows one at a time?

 

[TheGod1]

You say that on your actual worksheet SUMPRODUCT does not work. Does it return an error? If so, which error? Can you post the exact formula that you tried?

You also say that you'll have to use 7 conditions. Can you describe those conditions?

It returns a '0'.

The exact formula is below:

=SUMPRODUCT(--(($AC$7&"!A:A"="Offshore")+($AC$7&"!A:A"="Offshore/Domestic")),--($AC$7&"!B:B"=$M$3),--($AC$7&"!T:T"="06"),--($AC$7&"!E:E"="Y"),--($AC$7&"!J:J">$AC$7&"!K:K"))

 

[Domenic]

@ Ford

Nice catch!

@ TheGod1

First, I would convert those dates into true date values. This way you won't have any problems in any other future calculations that you may need. Here's how to do it quickly...

1) Select the whole column.

2) On the Ribbon, select Data > Text to Columns > Next > Next > under Column Data Format select Date > in the drop down arrow select "DMY" > Click Finish.

3) Do the same for any other column that contains dates formatted as text.

4) Then try the following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(INDIRECT("'"&$AC$7&"'!A2:A100"),{"Offshore","Offshore/Domestic"},0)),--(INDIRECT("'"&$AC$7&"'!B2:B100")=$M$3),--(INDIRECT("'"&$AC$7&"'!T2:T100")="06"),--(INDIRECT("'"&$AC$7&"'!E2:E100")="Y"),--(INDIRECT("'"&$AC$7&"'!J2:J100")>INDIRECT("'"&$AC$7&"'!K2:K100")))

or

=SUM(IF(ISNUMBER(MATCH(INDIRECT("'"&$AC$7&"'!A2:A100"),{"Offshore","Offshore/Domestic"},0)),IF(INDIRECT("'"&$AC$7&"'!B2:B100")=$M$3,IF(INDIRECT("'"&$AC$7&"'!T2:T100")="06",IF(INDIRECT("'"&$AC$7&"'!E2:E100")="Y",IF(INDIRECT("'"&$AC$7&"'!J2:J100")>INDIRECT("'"&$AC$7&"'!K2:K100"),1))))))

...confirmed with CONTROL+SHIFT+ENTER.

Adjust the ranges, accordingly. Try to avoid whole column references, which can be resource intensive.

Hope this helps!

 

[TheGod1]

@ Ford

Nice catch!

@ TheGod1

First, I would convert those dates into true date values. This way you won't have any problems in any other future calculations that you may need. Here's how to do it quickly...

1) Select the whole column.

2) On the Ribbon, select Data > Text to Columns > Next > Next > under Column Data Format select Date > in the drop down arrow select "DMY" > Click Finish.

3) Do the same for any other column that contains dates formatted as text.

4) Then try the following formula...

=SUMPRODUCT(--ISNUMBER(MATCH(INDIRECT("'"&$AC$7&"'!A2:A100"),{"Offshore","Offshore/Domestic"},0)),--(INDIRECT("'"&$AC$7&"'!B2:B100")=$M$3),--(INDIRECT("'"&$AC$7&"'!T2:T100")="06"),--(INDIRECT("'"&$AC$7&"'!E2:E100")="Y"),--(INDIRECT("'"&$AC$7&"'!J2:J100")>INDIRECT("'"&$AC$7&"'!K2:K100")))

or

=SUM(IF(ISNUMBER(MATCH(INDIRECT("'"&$AC$7&"'!A2:A100"),{"Offshore","Offshore/Domestic"},0)),IF(INDIRECT("'"&$AC$7&"'!B2:B100")=$M$3,IF(INDIRECT("'"&$AC$7&"'!T2:T100")="06",IF(INDIRECT("'"&$AC$7&"'!E2:E100")="Y",IF(INDIRECT("'"&$AC$7&"'!J2:J100")>INDIRECT("'"&$AC$7&"'!K2:K100"),1))))))

...confirmed with CONTROL+SHIFT+ENTER.

Adjust the ranges, accordingly. Try to avoid whole column references, which can be resource intensive.

Hope this helps!

Just wanted to say thank you both for your great tips.
I am going to shoot these ideas past my boss and see if he is willing to make some changes.
Your ideas have also got me thinking of some more ideas. Thanks so much!

GOOD NIGHT!

edit: I will post my final result if I get it to work.

 

[Domenic]

You're very welcome!