Counting with conditions in excel

Copper Contributor
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

@StevanBgd 

 

COUNTIF function

 

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)

 

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

@StevanBgd 

 

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)

@StevanBgd 

As variant for such model

image.png

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.