problem in countifs formula

[hello911helpme]

Capture.JPG
hi all,
i am trying to count the total no. of 'tea' from c2:f21 in this sheet for all 'monday'.
for this i am using =COUNTIFS($B$2:$B$21,"=Monday",$C$2:$F$21,"=tea") , but this is showing error, while its working for
=COUNTIFS($B$2:$B$21,"=Monday",$C$2:$C$21,"=tea") because here i am calculating the single column (c) only.
pls help to solve this problem.
is there any other formula to calculate from c2:f21?
thanks

[Richard Buttrey]

Hi,

=SUMPRODUCT((B2:B11="Monday")*(C2:F11="tea"))

seems to work.

[Jul Stev]

Hi ,

Try this formula

=SUMPRODUCT(($B$2:$B$21="=Monday")*($C$2:$F$21="=tea"))

[hello911helpme]

Hi,

=SUMPRODUCT((B2:B11="Monday")*(C2:F11="tea"))

seems to work.

Thanks Richard Buttrey....it worked.

[Richard Buttrey]

Thanks Richard Buttrey....it worked.

Indeed it did! I presume you spotted the typo - row 11 rather than row 21?

[hello911helpme]

Indeed it did! I presume you spotted the typo - row 11 rather than row 21?

it's ok.....
can i use this formula for calculating from sheet1 to sheet2 ? i mean data from sheet1 and result in sheet2. if possible....how?

[Richard Buttrey]

Hi,

Yes just preface the ranges with the relevant sheet ref. i.e.

=SUMPRODUCT((Sheet1!B2:B21="Monday")*(Sheet1!C2:F21="tea"))

[hello911helpme]

Hi,

Yes just preface the ranges with the relevant sheet ref. i.e.

=SUMPRODUCT((Sheet1!B2:B21="Monday")*(Sheet1!C2:F21="tea"))

thanks Richard....