Convert a sum formula to a count formula

[Guest]

Can anyone show me how to convert this formula to count cells rather than sum them

=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)),$O19:$BN19

I tried adding "counta" to the beginning, then to the last section but it didn't work
The formula is looking up columns with a month the same as DM$11 and summing the numbers within those corresponding cells - I need it to count the active cells rather than sum them

Any help is much appreciated.

 

[Peo Sjoblom]

=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)))

--

Regards,

Peo Sjoblom

Can anyone show me how to convert this formula to count cells rather than sum them?

=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)),$O19:$BN19)

I tried adding "counta" to the beginning, then to the last section but it didn't work.
The formula is looking up columns with a month the same as DM$11 and

summing the numbers within those corresponding cells - I need it to count
the active cells rather than sum them.

 

[Guest]

Thanks Peo but it isn't working for me
I wasn't clear enough and I think the end of the original formula was missed off too so here it is again

=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)),$O19:$BN19)*IF($A19="Pressing",$CP19,CY19

The above formula sums numbers entered across O19:BN19 and only includes those falling into the month nominated in DM11 x by either CP19 if they are nominated as "Pressing" in A19 or by CY19 for all others

My problem is that when "Job start" is entered into A19, the user nominates dates in cells O19:BN19 - not numbers and the above formula results are huge

I need to add some kind of IF argument to the above formula that says

If "Job start" is entered into cell A19, do the above calculation but count the dates rather than summing them and multiply by CY19, otherwise do as the above formula says

Hope that's clearer (but I don't think so)....
TFT

----- Peo Sjoblom wrote: ----

=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11))

--

Regards

Peo Sjoblo

Can anyone show me how to convert this formula to count cells rather tha sum them didn't work
The formula is looking up columns with a month the same as DM$11 an

summing the numbers within those corresponding cells - I need it to coun
the active cells rather than sum them

 

[Frank Kabel]

Hi
maybe this is what you want
=F(A19="Job
start",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)))*CY19,SUMPRODUCT
(--(MONTH($O$11:$BN$11)=MONTH(DM$11)),$O19:$BN19)*IF($A19="Pressing",$C
P19,CY19))



--
Regards
Frank Kabel
Frankfurt, Germany

Thanks Peo but it isn't working for me.
I wasn't clear enough and I think the end of the original formula was

missed off too so here it is again:

=SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)),$O19:$BN19)*IF($A19="P
ressing",$CP19,CY19)

The above formula sums numbers entered across O19:BN19 and only

includes those falling into the month nominated in DM11 x by either
CP19 if they are nominated as "Pressing" in A19 or by CY19 for all
others.

My problem is that when "Job start" is entered into A19, the user

nominates dates in cells O19:BN19 - not numbers and the above formula
results are huge.

I need to add some kind of IF argument to the above formula that says:

If "Job start" is entered into cell A19, do the above calculation but

count the dates rather than summing them and multiply by CY19,
otherwise do as the above formula says.

 

[Guest]

Thanks Fran
FYI - I had to add "/MONTH(BN$11) to get to to calculation to 1 - the result was still multiplying by the month number, and then I had to add an IF formula at the "Job Start" SUMPRODUCT to tell it not to calculate if the result was less than the rate (because if a rate only of was entered into CY19 it calculated an incorrect result - but thanks very much for getting me on the right track - that was a biggy

Final formula
=IF($A19="Job Start",IF(SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)))/MONTH(DM$11)*CY19<CY19,"",SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)))/MONTH(DM$11)*CY19),SUMPRODUCT(--(MONTH($O$11:$BN$11)=MONTH(DM$11)),$O19:$BN19)*IF($A19="Pressing",$CP19,CY19))