New Contributor

# Counting with conditions in excel

Hello everyone,
I am using Excel 2007 on Windows 10 and have some troubles with counting in excel. I will explain it in example:
DATE 1ST 2ND SHIPMENT
01.01.2020 Apple Banana yes
01.02.2020 Banana Apple yes
01.03.2020 Orange Banana no
01.04.2020 Apple Orange no
01.05.2020 Apple Orange yes
01.06.2020 Orange Banana yes
01.07.2020 Banana Apple yes
01.08.2020 Apple Orange yes
01.09.2020 Apple Banana yes
01.10.2020 Banana Apple yes
01.11.2020 Apple Banana yes
01.12.2020 Apple Orange no

I want to count several things:
1) how many times combination "apple" in column 1ST and "yes" in column SHIPMENT appears until column SHIPMENT turns "No" starting from last entered combination?
2) how many times combination "apple" in column 2ST and "yes" in column SHIPMENT appears until column SHIPMENT turns "No" starting from last entered combination?
3) how many times combination "apple" in column 1ST or column 2ND and "yes" in column SHIPMENT appears until column SHIPMENT turns "No" starting from last entered combination?
Thank you for help.
Kind regards
4 Replies

# Count numbers or dates based on a condition

I would be happy to know if I could help with this information.

Nikolino

I know I don't know anything (Socrates)

# Re: Counting with conditions in excel

Hi @NikolinoDE,

Thank you for the idea, but it seems to me that my problem needs some additional excel function besides CCOUNTIF(S). I do not know to solve it with just COUNFIF(S).
Obviously, it's too complex for my knowledge of Excel.
I'm too Socratian for Excel.

Kind regards,
StevanBgd

# Re: Counting with conditions in excel

Here is an old little example with names and amounts.

I wish you much success in your project.

Nikolino

I know I don't know anything (Socrates)

# Re: Counting with conditions in excel

As variant for such model

formulas are

``````in G2:
=IFNA(SUMPRODUCT(
(\$B\$2:INDEX(\$B\$2:\$B\$13,
MATCH(1,INDEX((\$B\$2:\$B\$13=F2)*(\$D\$2:\$D\$13="no"),0),0) ) =
F2)*
(\$D\$2:INDEX(\$D\$2:\$D\$13,
MATCH(1,INDEX((\$B\$2:\$B\$13=F2)*(\$D\$2:\$D\$13="no"),0),0) ) =
"yes")), SUMPRODUCT((\$B\$2:\$B\$13=F2)*(\$D\$2:\$D\$13="yes")))

In J2:
=IFNA(SUMPRODUCT(
(\$C\$2:INDEX(\$C\$2:\$C\$13,
MATCH(1,INDEX((\$C\$2:\$C\$13=I2)*(\$D\$2:\$D\$13="no"),0),0) ) =
I2)*
(\$D\$2:INDEX(\$D\$2:\$D\$13,
MATCH(1,INDEX((\$C\$2:\$C\$13=I2)*(\$D\$2:\$D\$13="no"),0),0) ) =
"yes")), SUMPRODUCT((\$C\$2:\$C\$13=I2)*(\$D\$2:\$D\$13="yes")))

In M2:
=IFNA(SUMPRODUCT(
(
(\$B\$2:INDEX(\$B\$2:\$B\$13,
MATCH(1,INDEX( ( (\$B\$2:\$B\$13=L2)+(\$C\$2:\$C\$13=L2) )*(\$D\$2:\$D\$13="no"),0),0) ) =
L2) +
(\$C\$2:INDEX(\$C\$2:\$C\$13,
MATCH(1,INDEX( ( (\$B\$2:\$B\$13=L2)+(\$C\$2:\$C\$13=L2) )*(\$D\$2:\$D\$13="no"),0),0) ) =
L2)
)
*
(\$D\$2:INDEX(\$D\$2:\$D\$13,
MATCH(1,INDEX(( (\$B\$2:\$B\$13=L2)+(\$C\$2:\$C\$13=L2) )*(\$D\$2:\$D\$13="no"),0),0) ) =
"yes")), SUMPRODUCT(( (\$B\$2:\$B\$13=L2)+(\$C\$2:\$C\$13=L2) )*(\$D\$2:\$D\$13="yes")))``````

Hope it shall work on 2007.