Ask Your Question
0

SUMIF that count occurrencies

asked 2016-10-20 11:03:35 +0200

Bri gravatar image

What I got

__A____|__B_______|__C______
_Name__|_discount_|_purchase
John___|__________|_x_______
Andrew_|_x________|_x_______
William|_x________|_________
Bart___|__________|_x_______

I want to count all purchases without discount (here will be 2 purchases).

SUMIF doesn't work because it need values to sum (when i place "1" instead of "x" in column C it works).

COUNTIF works separately in each column because doesn't have condition (giving 2 and 3 as a result).

Cannot use in this example count nonblank in one column and then count non blank in second then subtract discount occurrency is very important here.

CALC 5.2.1.2 PL

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-10-20 11:13:52 +0200

pierre-yves samyn gravatar image

Hi

=SUMPRODUCT(B2:B5<>"x";C2:C5="x")

Regards

edit flag offensive delete link more

Comments

Excellent :) Never thought about this function 'cos in polish it's called ~"SUMofMULTIPLICATION" (SUMA.ILOCZYNÓW) - and I do not multiply anything there, so was skipping it. Thank You

Bri gravatar imageBri ( 2016-10-21 08:14:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-20 11:03:35 +0200

Seen: 45 times

Last updated: Oct 20 '16