excel formula countif

  carmella 15:29 25 Jun 2003

I have an excel document with sheet 1,2 and 3.
In sheets 1 and 3 I enter values. Sheet 2 contains formulas which should calculate values based on what I've entered in sheets 1 and 3.

I have a CountIf fomula which works fine if I refer to sheet 1 only. I can't seem to work out how to enter a formula that will look for values in sheets 1 AND 3.

Here's the formula I have:
where Jan-May is sheet 1. Now I need to add Jun-Dec, which is sheet 3.

Any ideas?

many thanks

  VoG® 15:35 25 Jun 2003

COUNTIF can only test one condition. You need to use SUMPRODUCT

=SUMPRODUCT(('Jan-May'!D4:DG4="H") * ('Jun-Dec'!D4:DG4="H"))

  carmella 15:50 25 Jun 2003

I get a result False or True with this formula, whereas what I need is to add how many times the value "H" appears in sheets 1 and 3.

So if I entered "H" 3 times in sheet 1, and 4 times in sheet 3, the formula in sheet 2 should calculate 7.

thanks again

  Megatyte 15:53 25 Jun 2003

The * should be a +


  carmella 15:58 25 Jun 2003

I've now tried this:

and this:


but get results "0", which is not right.

any help appreciated.


  VoG® 16:02 25 Jun 2003

I just did a mock-up using three sheets and on the third sheet used


which returns the number of times "a" occurs in the same row of Sheets 1 and 2.

  Megatyte 16:02 25 Jun 2003

You are missing a set of parenthesis, After Sumproduct and at the end.


  VoG® 16:04 25 Jun 2003

Megatyte is correct if you want to return the result 7.

  carmella 16:26 25 Jun 2003

I hate to insist on this, but it doesn't work!
To make things simpler, I also tried it on a new spreadsheet, with values a in sheets 1 and 2, then the exact formula suggested, with all required parenthesis, and get a #False result. Without one set of parenthesis, I get 0.

sorry if I'm missing something right under my nsse here, but I think I am.

help, please.


  VoG® 16:49 25 Jun 2003

I've mailed you.

  Megatyte 17:18 25 Jun 2003

With the parenthesis as required and + instead of * you should get the right result. Using the * will return 0.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Converse draws on iconic heritage for a fresh brand identity

Mac power user tips and hidden tricks

Comment lancer Windows 10 en mode sans échec ?