Countif Query & sumif query help

[masond3]

hi

Sorry to keep bothering you guys like this, but i am either really stupid or excel doesn’t want to play ball. And i am thinking it’s the first option!

Okay i would like a countif function and also a sumif function

i have created hundreds of them today however this one doesn’t seem to work

sumif formula
Look at column A, if its Zoë Parry, look at column C. If column c = inbound enquiry, look at column D. If column D= docs out , look at column AB. If its = Close opportunity, look at column W and give me a grand sum

This should give tcv total of how many docs been sent out under each lead source which relates to the different stages .if i do this manually i can see that the value is £30855


countif formula
look at column A if its zoe parry, look at column C. If column c = inbound enquiry, look at column D. If column D= docs out , look at column AB. If its = Close opportunity
This should give me a total of how many docs been sent out under each lead source which relates to the different stages

if i do this manually i can see that there is 39records for this

if this is confusing I am sorry

i have created the chart on row AO

Which may give u a better understanding of what i am trying to achieve

please find a copy of my spreadsheet

[tigeravatar]

masond3,

You should use SumIfs(), not CountIfs(). Also, I applied filters to get a manual total of what the answer should be for:

Column | Criteria
A | "Zoe Parry"
C | "inbound enquiry"
D | "sent to risk"
AB | "Close opportunity"

Sum all matches in column W

And the answer is 0. There are no matches for all of that criteria. The only thing left in column AB after filtering columns A, C, and D with the above criteria, is "N/A"

[masond3]

Hi tigeravatar

thats correct, however there is certain conditions for example

as you can see, col C and D vary
so does col AB

Ab has 4 values, false, close opp, n.a and value

if you could help me write a formula for the situation i listed in the first post, i can adapt it too meet the other criteria

[tigeravatar]

=SUMIFS($W$2:$W$2862,$A$2:$A$2862,"zoe parry",$C$2:$C$2862,"inbound enquiry",$D$2:$D$2862,"sent to risk",$AB$2:$AB$2862,"close opportunity")

[masond3]

hi tigeravatar, looking into this further its seems like the formula is incorrect

i dont no whether i am explaining it incorrect or its impossible to do