1

I am trying to sum the lead count data having the condition that it should be >0 and the gender should be MALE. I don't want to use countifs because it is not supported by excelcius.

I have tried the following formula:

=IF('Universe data'!R2:R801>0,IF('Universe data'!H2:H801=MALE,COUNT('Universe data'!R2:R801)))

it gives me an #N/A error.

How can this be accomplished using COUNTIF?

Here is the data: The op i want is the sum of the males whose lead count is not 0 the answer should be 10 and how do i count the no of male and female the countif is giving either all the rows or eithr 0

gender    lead count      
--------  -----------
MALE            0
MALE            6
FEMALE          1
FEMALE          2
MALE            1
FEMALE          1
MALE            3
FEMALE          1
MALE            0
MALE            0

Thanks & regards

Oracle rookie
  • 95
  • 1
  • 2
  • 10
  • Please show us some data and tell us the values you expect to see from your formula. Also, do you want to use COUNTIF or not? – Bill the Lizard Apr 15 '13 at 13:00
  • Does Excelcius support `SumProduct`? If so, you could use the formula `=SumProduct(--(('Universe data'!R2:R801>0), --('Universe data'!H2:H801="MALE"))` to replace the countif – John Bustos Apr 15 '13 at 13:09

1 Answers1

4

I got a working solution to this. But it works only because the value you do not want to count is 0.

use =SUMIF(A:A;"MALE";B:B)

This is how it looks in my example: enter image description here

Edit: If you want to count male with a value above 0, and add 1 for each regardless of their value. Add another column And write this if statement in the column: =IF(B2<>0;1;0)

Then use this formula: =SUMIF(A:A;"MALE";C:C) enter image description here

Hope this is what you wanted. If not give me some feedback and ill try to adopt it. :)

Ole Henrik Skogstrøm
  • 6,353
  • 10
  • 57
  • 89
  • Hi ole and whatif i want to count the number of Male and female not equal to zero my count if is not working its giving all the count or either 0 – Oracle rookie Apr 16 '13 at 09:40
  • The problem with count if is that it also counts 0. The only real solution to this is to ether ad another column or to use `countifs`. If it is possible for you to ad a column with a formula i can show you how to calculate it with `countif`. – Ole Henrik Skogstrøm Apr 16 '13 at 10:27
  • ya show me no problem previously i did it with countifs but the software with which excel is integrated doesnot support countifs and sum ifs and suppose if there is another column say age group with values like 21-30,31-45,45+ u can assign any age group to the ablove excel then how do i sum them depending on the age group its getting complicated – Oracle rookie Apr 16 '13 at 10:30
  • This question is evolving, hehe ;) You can just change the variable if the different IF statements. Instead of "MALE" use "21-30". Btw. do you work in a media agency? :) – Ole Henrik Skogstrøm Apr 16 '13 at 10:41
  • hehe i am just a trainee accts just got into excel i thought it will be a piece of cake with countifs yaa and for the solution i want the age group,gender as well as the lead thats just the tip of the iceberg i am trying every thing ones ran outs of If,s hehe :) – Oracle rookie Apr 16 '13 at 10:46
  • Cool, a good tip is to watch a few courses at Lynda.com. After taking 1 or two excel courses on Lynda.com you are well suited to solve most problems that don't require macros. Regarding the problem with age groups try to set up all variables you want in different columns, and then think about what intermediary columns you need to calculate them and sum them up the right way. :) – Ole Henrik Skogstrøm Apr 16 '13 at 10:51
  • Also, if my answer solved you problem, remember to mark the question as answered. :) And if you work with marketing please support the new Marketing Q&A on area 51. http://area51.stackexchange.com/proposals/51786/marketing . – Ole Henrik Skogstrøm Apr 16 '13 at 10:53