Need to count the Specific Criteria by setting conditions in one formula

[Nani Ram]

Hello Everyone,

I'm looking for a some solution in excel to count the Specific Criteria by setting conditions in one formula, could you help me in this regard?

Question: I need the count of IT Services, Software, Telecom in J2, K2 and L2 cells by matching Week (Cell H2) and Name (Cell I2).

If we calculate manually its three IT services for Name A from the Week (Week-1 - 05-Jan To 09-Jan), so im trying to automate this one.

I'm sending you attached the sheet contains sample data.

Looking forward for your help.

Regards,
Naniram

 

[shrivallabha]

Welcome to Chandoo.org Excel Forums.

In cell J2 put following formula and then copy it down and across.
=SUMPRODUCT(--($A$2:$A$19=$H2),--($C$2:$C$19=$I2),--($E$2:$E$19=J$1))

 

[Nani Ram]

Welcome to Chandoo.org Excel Forums.

In cell J2 put following formula and then copy it down and across.
=SUMPRODUCT(--($A$2:$A$19=$H2),--($C$2:$C$19=$I2),--($E$2:$E$19=J$1))

Thank you very much!

Please could you explain me the symbols -- between the arrays.

Regards,
Naniram

 

[shrivallabha]

These are called as double unaries (--) which coerce Boolean(TRUE/FALSE) result into 1/0 respectively.

If you have not used SUMPRODUCT before then it will be good to go through following links to know its usages.
Read first three sticky posts in the following link:
http://www.vbaexpress.com/forum/forumdisplay.php?98-SUMPRODUCT-And-Other-Array-Functions

If you search Chandoo then you can find below one:
http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/

 

[Somendra Misra]

Hi,

Another formula can be in J2, copy down and across, :

=COUNTIFS($A$2:$A$19,$H2,$C$2:$C$19,$I2,$E$2:$E$19,J$1)

Regards,