Count how many qty's are greater than "0" (or blank) which make the SUMIF total

[ellison]

Hi, we are working on some info and are wondering if it's possible to find out how many of the raw quantities which make up the SUMIF total for each "info" are > zero, or blank.

Hopefully I've explained this a little better on this :

Countifs-But-With-Criteria-Like-Sumif-01.xlsx
	A	B	C	D	E
1	Row	Info	Raw Amt	SUMIF Amt For the Whole "Info"	COUNTIF of how many qty's for each "info" > 0 or Blank
2	2	alpha	1	6	2
3	3	alpha	0	6	2
4	4	alpha	5	6	2
5	5	beta		0	0
6	6	beta		0	0
7	7	charlie	16	92	3
8	8	charlie		92	3
9	9	charlie	31	92	3
10	10	charlie	45	92	3
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=SUMIF($B$2:$C$10,B2,$C$2:$C$10)

 

[dreid1011]

Hi, we are working on some info and are wondering if it's possible to find out how many of the raw quantities which make up the SUMIF total for each "info" are > zero, or blank.

Hopefully I've explained this a little better on this :

Countifs-But-With-Criteria-Like-Sumif-01.xlsx
	A	B	C	D	E
1	Row	Info	Raw Amt	SUMIF Amt For the Whole "Info"	COUNTIF of how many qty's for each "info" > 0 or Blank
2	2	alpha	1	6	2
3	3	alpha	0	6	2
4	4	alpha	5	6	2
5	5	beta		0	0
6	6	beta		0	0
7	7	charlie	16	92	3
8	8	charlie		92	3
9	9	charlie	31	92	3
10	10	charlie	45	92	3
Sheet1

Cell Formulas
Range	Formula
D2:D10	D2	=SUMIF($B$2:$C$10,B2,$C$2:$C$10)

Are you counting both criteria then? To my understanding, based on your criteria, info "beta" should show 2 in col E and info "charlie" should show 4 in col E, counting the number of raw amt both >0 and blank. If it's one or the other, then "beta" should still show 2?

That is if col E are the expected results?

 

[ellison]

Hi, yes Col E is expected results (hopefully!)

The reason that beta is "0" is that the corresponding amounts are both blank.
And charlie shows "3" as there is 1 blank and also 3 amounts which are greater than zero.

Hope that helps

 

[dreid1011]

Hi, yes Col E is expected results (hopefully!)

The reason that beta is "0" is that the corresponding amounts are both blank.
And charlie shows "3" as there is 1 blank and also 3 amounts which are greater than zero.

Hope that helps

So, when should it count blanks instead of amounts greater than 0?

 

[ellison]

Hopefully it will only count qty's > 0.

(In other words, not count blanks or zero qty's)

 

[dreid1011]

Hopefully it will only count qty's > 0.

(In other words, not count blanks or zero qty's)

Okay, well if you're hoping to never count blanks, give this a shot:

Book1
	A	B	C	D	E	F
1	Row	Info	Raw Amt	SUMIF Amt For the Whole "Info"	COUNTIF of how many qty's for each "info" > 0 or Blank
2	2	alpha	1	6	2	2
3	3	alpha	0	6	2	2
4	4	alpha	5	6	2	2
5	5	beta		0	0	0
6	6	beta		0	0	0
7	7	charlie	16	92	3	3
8	8	charlie		92	3	3
9	9	charlie	31	92	3	3
10	10	charlie	45	92	3	3
Sheet3

Cell Formulas
Range	Formula
D2:D10	D2	=SUMIF($B$2:$C$10,B2,$C$2:$C$10)
F2:F10	F2	=COUNTIFS($B$2:$B$10,B2,$C$2:$C$10,">0")

 

[ellison]

WOWOWOWOW, absolutely stunning - HUGE thanks!

 

[dreid1011]

WOWOWOWOW, absolutely stunning - HUGE thanks!

You're welcome.