Using MOD and COUNTIFS to find if a number is divisible by a certain cell

[jhgiants95]

So this is difficult to explain so see the table below. Essentially I have a sheet with all of the products listed in each column and the number of cases ordered in the rows below as orders come in. The first row on the sheet lists how many cases there are in a pallet for that specific item. I'm looking to count the number of pallets that aren't "Full" meaning don't divide evenly.

There are hundreds of columns so I don't think is practical to use countif one by one. I cant figure out what configuration of mod and count if I can do to have this repeat for hundreds of columns across the sheet. Thanks for any help

	Cases Per Pallet:	40	80	50	30
Customer	# "Non full Pallets"	Item2	Item3	Item4	Item5
Bill	2 (since the only items that don't divide evenly are the Item2 and item4) - Item 3 is simply two full pallets	45	160	105	30
Todd	0 - Since all are full pallets	80	320	150	150
Mark	4 - all of these are partial pallets	41	350	120	100

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(--(MOD(C3:F3,$C$1:$F$1)>0))

 

[jhgiants95]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(--(MOD(C3:F3,$C$1:$F$1)>0))

I just updated my profile thanks I didn't know I could do that. I keep getting a DIV/0 error with this

 

[jhgiants95]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(--(MOD(C3:F3,$C$1:$F$1)>0))

I'm troubleshooting now it works on about half the products and then when I set it to the full length I get the error

 

[jhgiants95]

Sorry for multiple replies. Your solution works and I figured out the problem - There are some empty cells that register as 0 in the first row - I can't figure out a working if statement to ignore those. Any ideas?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=SUMPRODUCT(--(MOD(C3:F3,$C$1:$F$1)>0))

 

[Fluff]

Do you have blank cells in the range?

 

[jhgiants95]

Yes see above. I cant seem to figure out how to ignore them

 

[Fluff]

Ok how about

=SUMPRODUCT(--(IFERROR(MOD(C3:P3,$C$1:$P$1),0)>0))

 

[jhgiants95]

Perfect, Thanks so much I think I have a much better understanding of how this formula works now. Sumproduct seems to be the best way to solve most of the multiple counting across ranges. Also, learning about MOD and the double unary. Thanks again

 

[Fluff]

You're welcome & thanks for the feedback.