COUNTIF, COUNTIFS or SUMIFS

[SpoctorDock]

Hi there,

I'm trying to sum the values of one column based on conditions in another column, for example:

A------B
No.----Cash
3------£1.10
4------£1.80
23-----£30.10
No.----Cash
0------£0.00


Where A is the number of copies and B is the payment method (cash or invoice).

There are blank cells in both the Cash and Invoice since it's one or the other and never both, so I'm trying to filter out any instances of "Cash", blank cells or those with £0.00 values.

So far I've managed to do

=COUNTIFS(F6:F95,"<>0",F6:F95,"<>Cash",F6:F95,"<>"&"")

and

=COUNTIF(F6:F95, "<>Cash")-COUNTIF(F6:F95,"")

as an alternative, both of which count the correct number of copies, but when I attempt using things like SUMIF etc to count the total copies sold based on the code above, I always get 0.

Does anyone have any other ways of getting the right total?

Cheers

 

[barry houdini]

Hello SpoctorDock, welcome to MrExcel

You should be able to modify the first formula to a SUMIFS, i.e. if sum range is in column E try

=SUMIFS(E6:E95,F6:F95,"<>0",F6:F95,"<>Cash",F6:F95,"<>")

 

[SpoctorDock]

Hi Harry,

Many thanks for your suggestion. It worked a treat!

I never knew there was a SUMIFS function. It's finally solved a week long dilemma