=sum(countif questions

[Graham C1600]

Hi,

I'm trying to get a count from excel based on certain criteria. The table i'm trying to get data from is similar to the below:-

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Simple[/TD]
[TD]Simon[/TD]
[TD]Pending[/TD]
[TD]Easy[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Simple[/TD]
[TD]Gary[/TD]
[TD]Batch 77[/TD]
[TD]Hard[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Complex[/TD]
[TD]Clare[/TD]
[TD]Holding[/TD]
[TD]Hard[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Simple[/TD]
[TD]Graham[/TD]
[TD]Batch 77[/TD]
[TD]Hard[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Complex[/TD]
[TD]Paul[/TD]
[TD]Batch 73[/TD]
[TD]Hard[/TD]
[/TR]
</tbody>[/TABLE]

The data above is populated by using VLOOKUPS on the numbers 1 to 5 from another sheet in the workbook.

What i need to try and do is say in cell A10, total the amount of the following (from column A it needs to = Simple, Column C needs to count all cells greater than Batch 74 (Batches run from 70 to 100 usually), and column D needs to = Hard.

I've tried various options but cannot get the formulas to work, not sure if it's because there are Pending and Holding in Column C.

I've tried a helper column converting column C to just display the right hand 2 digits but i still cannot get that to work correctly.

Any advice/help will be greatly appreciated.

Thanks.

 

[Eric W]

Either of these:

=SUMPRODUCT(--(A1:A5="Simple"),--(IFERROR(SUBSTITUTE(C1:C5,"Batch","")+0,0)>74),--(D1:D5="Hard"))

=SUM(COUNTIFS(A1:A5,"Simple",D1:D5,"Hard",C1:C5,"Batch "&{"8*","9*","","75","76","77","78","79","100"}))
both require Control+Shift+Enter.

 

[Graham C1600]

Thanks for this. Out of interest, what does the

"8*","9*"​

do in the function ?

 

[Ron Coderre]

If you're using standard naming conventions....try this:

B10: Simple
C10: >Batch
D10: 74
E10: Hard

A10: =SUMPRODUCT(($A$1:$A$6=B10)*(LEFT($C$1:$C$6,LEN(C10))=C10)*(--(0&MID($C$1:$C$6,FIND(" ",$C$1:$C$6&" ")+1,4))>D10)*($D$1:$D$6=E10))

Is that something you can work with?

 

[Aladin Akyurek]

In A10 control+shift+enter, not just enter:

=SUM(IF($A$1:$A$5="simple",IF(ISNUMBER(SEARCH("batch",$C$1:$C$5)),
    IF(REPLACE($C$1:$C$5,1,SEARCH(" ",$C$1:$C$5&" ")-1,"")+0>74,IF($D$1:$D$5="hard",1)))))

 

[Eric W]

You can see in the formula that I include 75, 76, 77, 78, and 79 individually, as they are all the values in the 70s greater than 74. All of the numbers in the 80s are (of course) greater than 74, but I don't need to list them individually. I can use the wildcard *, which matches anything. This means that anything that starts with "Batch 8" will be counted. "Batch 81", "Batch 88", even "Batch 8Z". But the last one seems unlikely to occur given your data.


Looks like I had a typo, the second formula should be:

=SUM(COUNTIFS(A1:A5,"Simple",D1:D5,"Hard",C1:C5,"Batch "&{"8*","9*","75","76","77","78","79","100"}))

 

[Graham C1600]

Hi,
I've tried your solution but all i get back is a #VALUE !. It works fine until i try to put the batch info in (C1:C5,"Batch "&{"8*","9*","75","76","77","78","79","100"})))

I've typed it out exactly as above.

any ideas ??

 

[Graham C1600]

All sorted thanks, i forgot to put a space in after the " for my batch numbers. Thanks for your help.

 

[Eric W]

Glad we could help.