Sum countifs

D

distillerjunkie

New member
Joined
Mar 16, 2016
Messages
33
Reaction score
0
Points
0
=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,1,'Jan 22-26'!E:E,2))

I need this to read 1 OR 2 not 1 AND 2. There might be no 1's, there might be no 2's. I need it to count them if they're available.

Essentially, If column A is "Andy Ryan", Column D is "Y", and Column E has a 1 and/or a 2, then SUM.
 
Last edited by a moderator:
Does this work?

Code: 
=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))

or if that doesn't work for you try:

Code: 
=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,{1,2}))
 
Last edited:
Something along the lines of:
=SUMPRODUCT((A1:A20="Andy Ryan")*(D1:D20="Y"),(E1:E20=1)+(E1:E20=2))
?
You can fill in the sheet references. Try not to use entire columns, they're very heavy on resources.
 
Last edited:
feels impossible

Hey thanks! This worked!

=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))

I need to add to this though now. I need it to look at another number in the same column as E. If there are 3's, count them and divide the total of 1's and 2's by the 3's to get a percentage. Honestly, i have no clue how to get this.

=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))
 
Perhaps?


Code: 
[COLOR=#0000FF][FONT=monospace]=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))/[/FONT][/COLOR][COLOR=#0000FF][FONT=monospace]SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,3))[/FONT][/COLOR]
 
Yes!

NBVC said:
Perhaps?


Code: 
[COLOR=#0000FF][FONT=monospace]=SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))/[/FONT][/COLOR][COLOR=#0000FF][FONT=monospace]SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,3))[/FONT][/COLOR]
Click to expand...


Thank you! This did work however, question: what if there are no 3's to count? Then it divides by 0 and i can't get a percent? It would technically be 100% if there are no 3's. Any idea how to make this work?

THANK YOU!!!
 
You can use IFERROR:

Code: 
[COLOR=#0000FF][FONT=monospace]=IFERROR(SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,"<=2"))/[/FONT][/COLOR][COLOR=#0000FF][FONT=monospace]SUM(COUNTIFS('Jan 22-26'!A:A,"Andy Ryan",'Jan 22-26'!D:D,"Y",'Jan 22-26'!E:E,3)),1)[/FONT][/COLOR]
 
You must log in or register to reply here.
Back
Top